dezinsektor
New Member
- Joined
- Sep 30, 2019
- Messages
- 25
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):
<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!
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!