# Array Lookup

#### EngEd

##### New Member
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.

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### Domenic

##### MrExcel MVP
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
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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,390
Messages
5,836,978
Members
430,464
Latest member
nickburrett

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

### Which adblocker are you using?

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

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