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.

Input Criteria 1	Input Criteria 2	Outputs
99	1	11
99	5	22
99	10	33
99	20	44
99	50	55
88	2	12
88	7	67
88	9	112
88	15	520
88	32	1113
88	60	5

<tbody>
</tbody>

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.

	A	B	C	D	E	F
1	Lookups			Criteria 1	Criteria 2	Outputs
2	Criteria 1	99		88	2	12
3	Criteria 2	2		88	7	67
4				88	9	112
5	Result	11		88	15	520
6				88	32	1113
7				88	60	5
8				99	1	11
9				99	5	22
10				99	10	33
11				99	20	44
12				99	50	55

<tbody>
</tbody>

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.

Lookups			Criteria 1	Criteria 2	Lookup_Ref	Outputs
Criteria 1	99		88	2	88002	12
Criteria 2	2		88	7	88007	67
			88	9	88009	112
Result	11		88	15	88015	520
			88	32	88032	1113
			88	60	88060	5
			99	1	99001	11
			99	5	99005	22
			99	10	99010	33
			99	20	99020	44
			99	50	99050	55

<tbody>
</tbody>

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.

Lookups			Criteria 1	Criteria 2	Lookup_Ref	Outputs
Criteria 1	99		88	2	88002	12
Criteria 2	2		88	7	88007	67
			88	9	88009	112
Result	11		88	15	88015	520
			88	32	88032	1113
			88	60	88060	5
			99	1	99001	11
			99	5	99005	22
			99	10	99010	33
			99	20	99020	44
			99	50	99050	55

<tbody>
</tbody>

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.