Multiple lookups for VLOOKUP?

[jrlafrance]

I've attached a sample of what I'm trying to do, but basically I need to reference the persons age and gender to the table, do determine if their time is greater than or less than the maximum time in the table. Then return an "X" if it's less. Is this possible?

[ragulduy]

You can use:
=VLOOKUP(C2,H3:R4,MATCH(D2,VALUE(LEFT(I1:R1,2)),1)+1)
to return the time from the table. It's an array formula so you need to confirm with ctrl+shift+enter. To determine if the time is greater than or less than you can extend it to:
=IF(E2<VLOOKUP(C2,H3:R4,MATCH(D2,VALUE(LEFT(I1:R1,2)),1)+1),"X","")
in F1, again an array formula.

[jhren]

...or non-array formula:

=IF(E2<VLOOKUP(C2,H3:R4,MATCH(D2,{17,22,27,32,37,42,47,52,57,62},1)+1),"X","")

[jrlafrance]

...or non-array formula:

=IF(E2<VLOOKUP(C2,H3:R4,MATCH(D2,{17,22,27,32,37,42,47,52,57,62},1)+1),"X","")

jhren: For some reason, your formula only works for "Male", not "Female", and I can't figure out how to fix it.

[jhren]

I only adapted the MATCH part of yudlugar's formula without trying it

=IF(E2<VLOOKUP(C2,H3:R4,MATCH(D2,{17,22,27,32,37,42,47,52,57,62},1)+1,FALSE),"X","")

...or...

=IF(E2<INDEX(H3:R4,MATCH(C2,H3:H4,0),MATCH(D2,{17,22,27,32,37,42,47,52,57,62},0)+1),"X","")