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>
</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
<tbody>
</tbody>
Thanks
=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