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:
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]")
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
Select the source data range (including the headers), go to Formulas > Create from Selection. Select Top row and Left column options and click OK.
To find the score of the student in the subject, construct the following INDIRECT INDIRECT formula in B5:
=IFERROR(INDIRECT(B3) INDIRECT(B4),"")
Your dropdown INDIRECT INDIRECT formula is ready! I hope this post helped you.
