Multivariable index match to closest value

[srehak]

I have tried to find some examples on here, but am coming up empty, if any of you have a good idea, please let me know.

Unfortunately with the way that excel deals with numbers, I am running into accuracy issues due to rounding errors, so I cannot do linear approximation for a project I am working on as the error is too much (lots of exponents etc- this has been verified by some other people as a problem for my application). As a result, i need to instead have a generated lookup table for reference instead.

it is based on 2 criteria, so in this case I need to use some form of a nested lookup function. Using the table below as a sample, I need to first find by the input criteria 1, then input criteria 2, and finally sort to the nearest value if it is not exact.
for example-
99, 2 will give 11.
88, 7 will give 67.

Finally, this is a function that will happen a couple thousand of times so I need to make it as "cpu friendly" as possible if that makes sense.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Input Criteria 1 [/TD]
[TD]Input Criteria 2[/TD]
[TD]Outputs[/TD]
[/TR]
[TR]
[TD]99[/TD]
[TD]1[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]99[/TD]
[TD]5[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]99[/TD]
[TD]10[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]99[/TD]
[TD]20[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]99[/TD]
[TD]50[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]88[/TD]
[TD]2[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]88[/TD]
[TD]7[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]88[/TD]
[TD]9[/TD]
[TD]112[/TD]
[/TR]
[TR]
[TD]88[/TD]
[TD]15[/TD]
[TD]520[/TD]
[/TR]
[TR]
[TD]88[/TD]
[TD]32[/TD]
[TD]1113[/TD]
[/TR]
[TR]
[TD]88[/TD]
[TD]60[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]



If you have any ideas on how I could handle this, it would be greatly appreciated.

Thank you for your time.

 

[Chrisdontm]

This was a bit tricky.
I put together an array formula for the ranges displayed for column A1:C12

I would suggest you name the ranges.

I believe I have it doing what you wanted it to do...
( let me know if I didn't follow you right )

I worked off of you entering your parameters into G2 and H2

Put this formula where ever you would like to display your answer...
But, it is an array formula, and must be entered with: Ctrl + Shft. + Enter

=IFERROR(IF(SUMPRODUCT(--($A$2:$A$12=$G$2),--($B$2:$B$12=$H$2),$C$2:$C$12)<>0,SUMPRODUCT(--($A$2:$A$12=$G$2),--($B$2:$B$12=$H$2),$C$2:$C$12),INDEX($A$2:$C$12,MATCH(IF(MIN(IF(--($A$2:$A$12=$G$2)*$H$2-$B$2:$B$12>0,--($A$2:$A$12=$G$2)*$H$2-$B$2:$B$12))<MIN(IF(--($A$2:$A$12=$G$2)*$B$2:$B$12-$H$2>0,--($A$2:$A$12=$G$2)*$B$2:$B$12-$H$2)),MIN(IF(--($A$2:$A$12=$G$2)*$H$2-$B$2:$B$12>0,--($A$2:$A$12=$G$2)*$H$2-$B$2:$B$12)),MIN(IF(--($A$2:$A$12=$G$2)*$B$2:$B$12-$H$2>0,--($A$2:$A$12=$G$2)*$B$2:$B$12-$H$2))),IF(MIN(IF(--($A$2:$A$12=$G$2)*$H$2-$B$2:$B$12>0,--($A$2:$A$12=$G$2)*$H$2-$B$2:$B$12))<MIN(IF(--($A$2:$A$12=$G$2)*$B$2:$B$12-$H$2>0,--($A$2:$A$12=$G$2)*$B$2:$B$12-$H$2)),--($A$2:$A$12=$G$2)*$H$2-$B$2:$B$12,--($A$2:$A$12=$G$2)*$B$2:$B$12-$H$2),0),3)),"")

 

[kvsrinivasamurthy]

Try this ARRAY formula
F3=99, G3=2, Result=11
F3=99, G3=6, Result=22 as 6 is nearer to 7 rather than 1

=INDEX($C$2:$C$12,SMALL(IF(ABS(IF($A$2:$A$12=F3,$B$2:$B$12-G3,99^9))=SMALL(ABS(IF($A$2:$A$12=F3,$B$2:$B$12-G3,1)),1),ROW($A$2:$A$12),""),1)-ROW($A$2)+1)

ARRAY formula is used


To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets by excel.

 

[thisoldman]

You can try this.

Sort your current lookup table on Input Criteria 1. Now an "approximate" match can be used to retrieve the value using INDEX+MATCH—faster than an exact match.

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Lookups[/TD]
[TD][/TD]
[TD][/TD]
[TD]Criteria 1[/TD]
[TD]Criteria 2[/TD]
[TD]Outputs[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Criteria 1[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Criteria 2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]67[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]112[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Result[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]520[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]1113[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]55[/TD]
[/TR]
</tbody>[/TABLE]

Single-cell array formula in B5: type the formula and then press Ctrl+Shift+Enter, not just Enter.

=INDEX($G$3:$G$13,MATCH(C3*100+C4,$E$3:$E$13*100+$F$3:$F$13,1))

I'm assuming Criteria 2 will be < 100.

 

[thisoldman]

I forgot to adjust the formula when I added row and column headers in my previous post:

=INDEX($F$2:$F$12,MATCH(B2*100+B3,$D$2:$D$12 *100+$E$2:$E$12,1))

If Criteria 2 could be greater than or equal to 100 but less than 1000, adjust the formula to:

=INDEX($F$2:$F$12,MATCH(B2*1000+B3,$D$2:$D$12*1000+$E$2:$E$12,1))

Essentially, the search is done on concatenated digits: =Criteria_1 & Criteria_2, where the ampersand is the concatenation operator.

 

[thisoldman]

I hate the idea of several thousand array formulas. This requires you to add a column to the sorted table but the look-ups should be reasonably speedy.

[TABLE="class: grid"]
<tbody>[TR]
[TD]Lookups
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Criteria 1[/TD]
[TD]Criteria 2[/TD]
[TD]Lookup_Ref[/TD]
[TD]Outputs[/TD]
[/TR]
[TR]
[TD]Criteria 1
[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]88002[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Criteria 2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]88007[/TD]
[TD="align: right"]67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]88009[/TD]
[TD="align: right"]112[/TD]
[/TR]
[TR]
[TD]Result[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]88015[/TD]
[TD="align: right"]520[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]88032[/TD]
[TD="align: right"]1113[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]88060[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]99001[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]99005[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]99010[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]99020[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]99050[/TD]
[TD="align: right"]55[/TD]
[/TR]
</tbody>[/TABLE]

The formula in the column "Lookup_Ref" is copied downward:
=1000*D2+E2

The returned value in "Result" is from this non-array look-up formula:
=INDEX(G2:G12, MATCH(1000*B2+B3, F2:F12))

 

[srehak]

I hate the idea of several thousand array formulas. This requires you to add a column to the sorted table but the look-ups should be reasonably speedy.

[TABLE="class: grid"]
<tbody>[TR]
[TD]Lookups
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Criteria 1[/TD]
[TD]Criteria 2[/TD]
[TD]Lookup_Ref[/TD]
[TD]Outputs[/TD]
[/TR]
[TR]
[TD]Criteria 1
[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]88002[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Criteria 2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]88007[/TD]
[TD="align: right"]67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]88009[/TD]
[TD="align: right"]112[/TD]
[/TR]
[TR]
[TD]Result[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]88015[/TD]
[TD="align: right"]520[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]88032[/TD]
[TD="align: right"]1113[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]88060[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]99001[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]99005[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]99010[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]99020[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]99050[/TD]
[TD="align: right"]55[/TD]
[/TR]
</tbody>[/TABLE]

The formula in the column "Lookup_Ref" is copied downward:
=1000*D2+E2

The returned value in "Result" is from this non-array look-up formula:
=INDEX(G2:G12, MATCH(1000*B2+B3, F2:F12))

Interesting. Thank you!

 

[thisoldman]

Glad you find it worth looking into.

The other way to do the concatenation is with
=D2&" "&E2

The lookup then becomes
=INDEX(G2:G12, MATCH(B2&" "&B3, F2:F12))

You need a way to keep Criteria1 and Criteria2 from stepping on each other while still combining them. These versions with the text operation are a little more foolproof, but text operations are usually just a fraction of a blink slower. I wouldn't notice the difference in most spreadsheets.