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):

Column E Variable_3	Column F Variable_2	Column G Result (Q)	Column I Variable_1
5	31.5	0.5	72.4
5.5	32	1	65.28
6	32.5	2	66.5
6.5	33	1.5	66

<tbody>
</tbody>

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]