In Excel, to build an IF AND statement based on the selection of the dropdown list, combine the IF and AND functions in one formula and use the Data Validation feature.
IF tests the condition and returns a value if the condition evaluates to TRUE or another value if FALSE. AND tests the conditions and returns TRUE if all of the conditions evaluate to TRUE, FALSE otherwise.
I have the following dataset. Now I want the program to return the exam 1 total in B26 and exam 2 total in B27 when the user makes the student selection from the dropdown in B25. On returning the total of both the exams, I want to construct the IF AND statement in B28 that must read as follows: Student whose exam 1 total is greater than or equal to 40 and exam 2 total is greater than or equal to 60 are pass, fail otherwise.
To accomplish the task, I perform the following steps:
Use data validation to create Name dropdown list in B25. To do so, go to Data > Data Validation > Allow: List > Source: =$A$5:$A$12
To find the exam 1 total of the student, enter the following INDEX MATCH formula in B26:
=IFNA(INDEX(D5:D12,MATCH(B25,A5:A12,0)),"")
To find the exam 2 total of the student, enter the following INDEX MATCH formula in B27:
=IFNA(INDEX(D16:D23,MATCH(B25,A16:A23,0)),"")
To find the result (Pass/Fail) of the student, construct the IF AND formula in B28, like shown below:
=IF(AND(B26>=40,B27>=60),"Pass","Fail")
Your dropdown IF AND formula is ready! I hope this post helped you.
