Multiple Column Lookup

[CurlerBob]

I'm trying to lookup a grouping value based on gender, height, and weight.
My data looks like this gender/height/weight/group:
Male/4'10'/0/Group 1
Male/4'10/81/Group 2
Male/4'10/91/Group 3
Male/4'10/134/Group 2
Male/4'10/158/Group 4
Male/4'10/181/Group 1
Male/4'11'/0/Group 1
Male/4'11/84/Group 2
Male/4'11/94/Group 3
Male/4'11/138/Group 2
Male/4'11/163/Group 4
Male/4'11/188/Group 1

Where the lookup will return the group for the range up to the next value.
SO for the first line the range is 0-80, second line 81-90, etc. with
anything over 181 returning Group 1.

I tried concatinating the three lookup columns together but that doesn't
work. I'm certain there must be a relatively simple way to do this lookup
but I can't seem to puzzle it out.

 

[Sheeloo]

Did you use TRUE as the fourth parameter of VLOOKUP?
Also first column in the lookup range should be sorted... so you need to
sort after combining the columns...

I had written a detailed post but it did not get posted...

 

[T. Valko]

You need to remove the extra apostrophes in:

Male/4'10'/0/Group 1
Male/4'11'/0/Group 1

With your data in the range A112

Lookup values:

F1 = Male
G1 = 4'11
H1 = some number

Array entered** :

=INDEX(D112,MATCH(1,(A1:A12=F1)*(B1:B12=G1)*(C1:C12=MAX(IF((A1:A12=F1)*(B1:B12=G1)*(C1:C12<=H1),C1:C12))),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[CurlerBob]

Thank you! This works great! I'll have to do some further research on array
formulas so that I can use this sort of logic in the future. Thanks again!

 

[T. Valko]

You're welcome. Thanks for the feedback!