|Okay - I think I've got it now - Thanks to DerbyDad's "trick"|
Here's what I believe the objective is;
The table array contains predetermined values for different viscosities. The objective is to calculate for values that do not appear in the table e.g.
The user wants to calculate viscosities for the value 39.45. The table contains values for 39 and 39.5. The objective is to return the 2 values - the ones directly lower and higher than the entered value so that the associated measures can be interpolated for the target.
The IndexMatch statement is taking the absolute difference of the each table entry minus the target value input in F16 and finding the minimum value of all those differences.So, in the example provided the target value is 39.45. The minimum point of the absolute differences appears at 39.5 where the OP really needs 39. But the difference between 39 and 39.45 is greater than the difference between 39.5 and 39.45.
That is why (if all the above is correct) my suggested formula will work - the index/match is looking for the closest match to 39.45 that is less than 39.45. In this case 39.45 will return 39.
As to the second problem;
if there is a perfect match even then it take inputs for interpolation. I want incorporate this error in index match formula when i used if command with it indexmatch formula didnt work
I'm not sure what the user is after. I can see that if a perfect match is entered it will return the match as well as the next highest value. The interpolation still works in that it shows the correct values for the entered value.
I need to understand what the user wants to see when a perfect match is entered.