Multiple Column Lookup

  • Thread starter Thread starter CurlerBob
  • Start date Start date
C

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.
 
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...
 
You need to remove the extra apostrophes in:
Male/4'10'/0/Group 1
Male/4'11'/0/Group 1
Click to expand...

With your data in the range A1:D12

Lookup values:

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

Array entered** :

=INDEX(D1:D12,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)
 
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!
 
Back
Top