How to do a conditional Vlookup

Question

I have a table like the picture below

My purpose I to look up for the score of A2 in class B WITH a formula (no sort, filter, ..) - could it be done in Excel 2010?

Answer 1

Assuming that all the names in each class are unique, and that the score is always a number... MAXIFS is the simplest solution I can think of:

=MAXIFS($C$2:$C$7,$A$2:$A$7,$E2,$B$2:$B$7,$F2)

EDIT - Added a solution for older Excel versions

You could also use an INDEX/MATCH formula with multiple criteria:

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

Answer 2

Try this:

{=INDEX($C$2:$C$7,MATCH(E2&F2,$A$2:$A$7&$B$2:$B$7,0),1)}

You need to key in the formula:

=INDEX($C$2:$C$7,MATCH(E2&F2,$A$2:$A$7&$B$2:$B$7,0),1)

And then, before hitting enter, hold down "control+shift" while hitting enter. This causes the formula to be an "array" formula. If you just hit enter the formula doesn't work.

Here's what my test looked like on screen:

The formula is in G2.

If you use R1C1 mode (which everyone should because it is much easier to debug spreadsheets) then the formula looks like this:

{=INDEX(R2C3:R7C3,MATCH(RC[-2]&RC[-1],R2C1:R7C1&R2C2:R7C2,0),1)}