Two-way lookup with INDEX and MATCH return closest match

mschelle6

New Member
Joined
Sep 28, 2009
Messages
6
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
 

Some videos you may like

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
Joined
Apr 4, 2009
Messages
3,680
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
Joined
Sep 28, 2009
Messages
6
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
Joined
Aug 23, 2010
Messages
16,371
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:

Watch MrExcel Video

Forum statistics

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