Finding approximate value based on interpolation of data

[dezinsektor]

Hello,

I need help with this problem of finding the closest result based on 3 criteria.
Using index and match I get the result but only if the result value and the variables are exact match.

These are the measurements that I get from device:
Variable_1 - B2=59.3
Variable_2 - B3=31.3
Variable_3 - B4=5.2
________
Result (Q) B5=?

So I need to match these variables with data table (of course that the table is much bigger, it has over 32.000 cells):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column E
Variable_3[/TD]
[TD]Column F
Variable_2[/TD]
[TD]Column G
Result (Q)[/TD]
[TD]Column I
Variable_1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]31.5[/TD]
[TD]0.5[/TD]
[TD]72.4[/TD]
[/TR]
[TR]
[TD]5.5[/TD]
[TD]32[/TD]
[TD]1[/TD]
[TD]65.28[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]32.5[/TD]
[TD]2[/TD]
[TD]66.5[/TD]
[/TR]
[TR]
[TD]6.5[/TD]
[TD]33[/TD]
[TD]1.5[/TD]
[TD]66[/TD]
[/TR]
</tbody>[/TABLE]

To explain, if variable_3=5, variable_2=31.5 and variable_1=72.4, the result is 0.5.
But if only one of the variables don't match I get no result (#N/A) with index match.
And the problem is that sometimes none of the variables don't match, then I need to find closest matches of 3 variables and get the result.

This is the formula I use:
={INDEX($G$13:$G$113;MATCH(1;(B2=$I$13:$I$113)*(B3=$F$13:$F$113)*(B4=$E$13:$E$113);0))}

If VBA is needed to solve this I would gladly accept it as a solution to this problem.


Thank you very much!

 

[Eric W]

Happy to help!

 

[dezinsektor]