Two-way lookup with INDEX and MATCH return closest match

[mschelle6]

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

<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>
[TD="class: xl66, width: 192, colspan: 3"]Frame
United Inches
[/TD]
[TD="class: xl69, width: 174, colspan: 2"]7/8
25
[/TD]

[TD="class: xl66, colspan: 3"]Width of Molding in Inches
[/TD]
[TD="class: xl71, colspan: 2"]4.7[/TD]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1/2"
[/TD]
[TD]1"
[/TD]
[/TR]
[TR]
[TD]24 Feet
[/TD]
[TD]4.7
[/TD]
[TD]5.0
[/TD]
[/TR]
[TR]
[TD]26 Feet
[/TD]
[TD]5.3
[/TD]
[TD]5.7[/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[James006]

Hello,

You could test following:

[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]

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

Below is my criteria


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Width
[/TD]
[TD]7/8
[/TD]
[/TR]
[TR]
[TD]UI
[/TD]
[TD]25
[/TD]
[/TR]
[TR]
[TD]Width of Molding
[/TD]
[TD]4.7
[/TD]
[/TR]
</tbody>[/TABLE]


Table where information is pulled
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]UI
[/TD]
[TD]Width
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/2"
[/TD]
[TD]1"
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]4.0
[/TD]
[TD]4.3
[/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]4.3
[/TD]
[TD]4.7
[/TD]
[/TR]
[TR]
[TD]24
[/TD]
[TD]4.7
[/TD]
[TD]5.0
[/TD]
[/TR]
[TR]
[TD]26
[/TD]
[TD]5.0
[/TD]
[TD]5.3
[/TD]
[/TR]
[TR]
[TD]28
[/TD]
[TD]5.3
[/TD]
[TD]5.7
[/TD]
[/TR]
</tbody>[/TABLE]

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]

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.