In Excel, to extract score of the student in the subject based on the selection of the dropdowns, you can use data validation and the INDIRECT INDIRECT formula.
The following dataset contains scores of students in 3 subjects:
![Extracting score of the student in the subject based on the selection of the dropdowns Extracting score of the student in the subject based on the selection of the dropdowns](https://kunalexceltip.com/wp-content/uploads/2024/04/Extracting-score-of-the-student-in-the-subject-based-on-the-selection-of-the-dropdowns.gif)
Now I want to find the score in B5, when the user makes a selection from the student and subject dropdown lists in B3 and B4 respectively.
To accomplish the task, I perform the following steps:
Use data validation to create Student dropdown list in B3, listing student names in column A. To do so, go to Data > Data Validation > Allow: List > Source: =INDIRECT("data[Student]")
![Using data validation to create dynamic dropdown list Using data validation to create dynamic dropdown list](https://kunalexceltip.com/wp-content/uploads/2024/04/Using-data-validation-to-create-dynamic-dropdown-list.png)
Use data validation again to create Subject dropdown list in B4, listing subjects in row 7. To do so, go to Data > Data Validation > Allow: List > Source: =$B$7:$D$7
![Using data validation to create dropdown list Using data validation to create dropdown list](https://kunalexceltip.com/wp-content/uploads/2024/04/Using-data-validation-to-create-dropdown-list.png)
Select the source data range (including the headers), go to Formulas > Create from Selection. Select Top row and Left column options and click OK.
![Creating names from selection Creating names from selection](https://kunalexceltip.com/wp-content/uploads/2024/04/Creating-names-from-selection.png)
To find the score of the student in the subject, construct the following INDIRECT INDIRECT formula in B5:
=IFERROR(INDIRECT(B3) INDIRECT(B4),"")
![Constructing the INDIRECT INDIRECT formula Constructing the INDIRECT INDIRECT formula](https://kunalexceltip.com/wp-content/uploads/2024/04/Constructing-the-INDIRECT-INDIRECT-formula.png)
Your dropdown INDIRECT INDIRECT formula is ready! I hope this post helped you.