Array Lookup

EngEd

New Member
Joined
Sep 24, 2014
Messages
1
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 Inch3 Inch4 Inch5 Inch
Type A221652
Type B161341
Type C10820
Type D6310

<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.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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

Well-known Member
Joined
May 11, 2010
Messages
612
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,337
Messages
5,601,026
Members
414,422
Latest member
acegreen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top