In Excel, to vertical-lookup with if condition based on the selection of the dropdown list, I will use an Excel table, data validation to create dynamic dropdown and the IF VLOOKUP formula.
IF tests a condition and returns a value if true or another value if false. VLOOKUP looks up the value in the left-most column of the source data and returns the corresponding value from the right column.
I have the following dataset. Now I want to find if a student is pass or fail in F2 based on the student selection from the dropdown in F1. A student is pass only if their score is greater than or equal to 40.
To accomplish the task, I perform the following steps:
Convert the source data into an Excel table and name it as data. To do so, select the source data range (including the headers), go to the Insert tab and click on the Table button.
Use data validation to create Student dropdown list in F1, listing student names in column A. To do so, go to Data > Data Validation > Allow: List > Source: =INDIRECT("data[student]")
To find the pass/fail result of the student, construct the IF VLOOKUP formula in F2, like shown below:
To replace the N/A error with an empty string "", nest the entire formula in the IFNA function.
Your dropdown IF VLOOKUP formula is ready! I hope this post helped you.
