Find row, find value, then return column heading

[jimbrownle]

I have the height and weight, and want to return the weight class (column header) from a height/weight table (example below). Weights are in a range, so for someone 63 inches, if they weight 115 lbs, I want to return Class A. For someone who is 64 inches 155 lbs, return Class C (or Class D - I can adjust the headers).

Ht .............Class A.......Class B..........Class C..........Class D
62" .............105 lbs ......125...............140...............165
63" .............110 ...........130...............142...............168
64" .............113 ...........135...............148................172

The actual data is a 17x25 array, so i don't think nested if/then will work.

The weight won't necessarily be an exact match, so I need it to pull the next highest value (or next lowest value - either will work) like a "True" statement in a vlookup.

I would prefer a formula solution if possible, because VB causes flags in our environment, but if VB is the only answer I will take it.

I'm 4 hrs into this and can't see line of sight to a solution, so any help would be appreciated.

Thanks,

-Jim

 

[John_w]

You need some sort of double or two-dimensional lookup formula. For example see http://technet.microsoft.com/en-us/library/cc750573.aspx and http://www.cpearson.com/excel/TablesAndLookups.aspx.

 

[Scott Huish]

First I would make sure your tables values contain only numbers. If you want text do display with it, use number formatting

In other words for height, don't have 62" in the cell have 62 with a formatting of 0\"

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Ht	Class A	Class B	Class C	Class D		Height	Weight	Class
2	62"	105	125	140	165		62	125	Class B
3	63"	110	130	142	168
4	64"	113	135	148	172
Sheet1

 

[jimbrownle]

Perfect!!! Exactly what I needed!!!