# Two-way lookup with INDEX and MATCH return closest match

#### mschelle6

##### New Member
I'm working on a spreadsheet and have run into a issue using Two-way lookup with INDEX and MATCH. If the value is not in the table, I need excel to use the next highest value. Below is the formula I'm using.

=INDEX('LarsonTable'!B4:K54,MATCH(Work!F20,'LarsonTable'!A4:A54,1),MATCH(Work!F16,'LarsonTable'!B3:K3,1))

This formula is not showing the next highest number

 Frame United Inches 7/8 25 Width of Molding in Inches 4.7

<colgroup><col style="width:48pt" width="64" span="4"> <col style="mso-width-source:userset;mso-width-alt:4022;width:83pt" width="110"> </colgroup><tbody>
</tbody>

Since 7/8 or 25 is not in the table, I need the formula to use 26 and 5.7, but current it's using 24 and 4.7

 1/2" 1" 24 Feet 4.7 5.0 26 Feet 5.3 5.7

<tbody>
</tbody>

Thanks

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### James006

##### Well-known Member
Hello,

You could test following:

Code:
``[COLOR=#333333]=INDEX('LarsonTable'!B4:K54,MATCH(Work!F20,'LarsonTable'!A4:A54)+1,MATCH(Work!F16,'LarsonTable'!B3:K3)+1)[/COLOR]``
Hope this will help

#### mschelle6

##### New Member
This worked, but now it's adding an extra number when I have an exact match

Below is my criteria

 Width 7/8 UI 25 Width of Molding 4.7

<tbody>
</tbody>

Table where information is pulled
 UI Width 1/2" 1" 20 4.0 4.3 22 4.3 4.7 24 4.7 5.0 26 5.0 5.3 28 5.3 5.7

<tbody>
</tbody>

In this example, I need to use the UI of 26 and the Width of 1", to get a Width of Molding of 5.3. My current formula is pulling the UI of 24 and the Width of 1/2" making me have a Width of Molding of 4.7.
If I have a UI of 28 and a width of 1/2", I need to use the exact value which will equal Width of Molding of 5.3

My current formula is
=INDEX('LarsonTable'!B4:K54,MATCH(Work!F20,'LarsonTable'!A4:A54,1),MATCH(Work!F16,'LarsonTable'!B3:K3,1))

#### Marcelo Branco

##### MrExcel MVP
My current formula is
=INDEX('LarsonTable'!B4:K54,MATCH(Work!F20,'LarsonTable'!A4:A54,1),MATCH(Work!F16,'LarsonTable'!B3:K3,1))
See if this works
=INDEX(LarsonTable!B4:K54,MATCH(Work!F20,LarsonTable!A4:A54,1)+1*ISNA(MATCH(Work!F20,LarsonTable!A4:A54,0)),MATCH(Work!F16,LarsonTable!B3:K3,1)+1*ISNA(MATCH(Work!F16,LarsonTable!B3:K3,0)))

M.

Last edited:

1,102,872
Messages
5,489,430
Members
407,688
Latest member
Jerry1383320

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...