Array Lookup

[EngEd]

I have a lookup situation that has baffled my personal Excel abilities, and I don't really know how to even search for what I need. Any help or advice would be greatly appreciated.

I have a static data table of the format:

	2 Inch	3 Inch	4 Inch	5 Inch
Type A	22	16	5	2
Type B	16	13	4	1
Type C	10	8	2	0
Type D	6	3	1	0

<tbody>
</tbody>

The numeric data is arranged in rows, in descending order left to right, always integers, never negative. This table is an example - the actual dataset is many more rows and columns. Each row and each column has a title field at the left/top.

I need a formula that can take an argument Type A thru Type D, and a numeric value, and return the column header for the column with the minimum value in the row indicated by the Type argument that is greater than or equal to the numerical argument value. For example, if the arguments are 'Type C' and '9', it should return '2 Inch'. If the argument is 'Type A' and '5', it should return '4 Inch'.

I figured out the formula =INDEX(A1:E5,1,MATCH(NUMBER,B3:E3),-1)) which seems to work to return the header provided that the row to look in is static. However, the Type A-D argument in my situation is a calculated value by other parts of the spreadsheet, and I don't know how to make the range inside the MATCH command move to different rows based on that value. The real data set has too many rows for a nested IF statement group.

 

[Domenic]

Try...

=INDEX($B$1:$E$1,MATCH(H2,INDEX($B$2:$E$5,MATCH(G2,$A$2:$A$5,0),0),-1))

...where G2 contains the type, such as Type A, and H2 contains a number, such as 9.

Hope this helps!

 

[Trouttrap2]

You can try this: This provides an error if the type and numeric value are not found in the table.
=INDEX($B$1:$E$1,1,MATCH(C12,OFFSET(B2,MATCH(B12,A2:A5,0)-1,,,4),0))

B12 = Type; C12 = numeric value