Excel data extraction

Copper Contributor

Hi Community 


I am trying to establish a way where i can select Via a drop down or other way , select a "Profile/Position" that will also link its corresponding learning courses , that than will pull the finer course details from a large list and provide a simplified end assessment 




1 Reply


To achieve this in Excel, you can use a combination of data validation, VLOOKUP function, and conditional formatting. Here is a step-by-step guide:

  1. Set up your data:
    • Create a table with the "Profile/Position" column, the corresponding "Learning courses" column, and the "Course details" column.
    • Fill in the table with the relevant data.
  2. Create a drop-down list:
    • On a separate sheet, create a drop-down list of the available "Profile/Position" options. You can use data validation to create the list.
    • Select a cell where you want the drop-down list to appear.
    • Go to the Data tab, click on Data Validation, choose "List" as the validation criteria, and select the range containing the "Profile/Position" options.
  3. Retrieve corresponding learning courses:
    • In another cell, use the VLOOKUP function to retrieve the corresponding learning courses based on the selected "Profile/Position" from the drop-down list.
    • The formula may look like this: =VLOOKUP(SelectedProfile,ProfileCourseTable,2,FALSE).
      • "SelectedProfile" is the cell reference of the drop-down list.
      • "ProfileCourseTable" is the range of your table containing the "Profile/Position" and "Learning courses" columns.
  4. Display course details:
    • Use conditional formatting to highlight or display the course details based on the selected learning course.
    • Create rules using formulas that compare the selected learning course with the course details in the table.
    • Apply the formatting or display the relevant course details when a match is found.

By following these steps, you can create a dynamic system where selecting a "Profile/Position" from the drop-down list will automatically populate the corresponding learning courses and display the associated course details.