Vlookup with a criteria

[doritos]

Would it be possible to do vlookup based on a certain criteria? For eg. if the first item is not satisfactory, it will continue to look at the next.

For example, i have three columns - grade, names and exam results.

Grade 8 Abel 70%
Grade 8 Bonnie 90%
Grade 7 Caleb 30%
Grade 7 Dawn 90%
Grade 6 Ethan 50%
Grade 6 Fiona 40%

I want to pick one person from Grade 8. But the criteria needs to be > 80%. So my formula should skip Abel and go straight to Bonnie. Is there a way to do vlookup with a criteria? Perhaps using the "IF" function? Thanks! Help is much appreciated.

My actual list would contain many items - eg. 30 rows of each grade.

[NBVC]

If your table is in say,. A2:C7, and you have inputs in F2 and G2 of the grade and percentage in F2 and G2, respectively, then try formula:

=INDEX($B$2:$B$7,MATCH(1,INDEX(($A$2:$A$7=F2)*($C$2:$C$7>=G2),0),0))

adjust references to suit.

[doritos]

Thank you!!! Much appreciated. Tried it out on the small data set and it worked! Now just need to try it out on my actual data. Crossing my fingers!

[doritos]

Hi. I tried it on my actual data. Worked like a gem! Thank you so much. Really appreciate your advice!