Help with simplification of IF/OR formula

[dimexi]

Hi All, I was wondering if I could get some help/advice.

I have two tables illustrated below:

Table 1

X values Y Values

1 100

2 200

3 300

4 400

5 500

6 600

7 700



Table 2

Entered X value Linearly interpolated Y

2.56

3.72

4.69

5.82



Now, table 2 is a user input, and my problem is getting a simplistic formula that can look up the entered X value in table 1, if it is not exact, linearly interpolate ONLY between 2 points and return the answer. For example, for 2.56, I would like the formula to go to table 1 and linearly interpolate between 2 and 3 and give the Y value.

I know I can achieve this with nested If/Or statements, that is, I could use =IF(OR(G11>B3,G11<B4,G11=B3,G11=B4),FORECAST(G11,C3:C4,B3:B4),"no") and in place of “no”, I would nest another If and OR to look at the next two pairs. The problem is there is a limit of 7 nested Ifs.

Can anyone help? I suspect the answer is an array formula, but I am not the best at those.



Hope it is clear, and Thank you

 

[jasonb75]

What is the purpose of {1} as the logical test?

{1} always returns TRUE as an array, this ties up with the earlier points of discussion. +{0,1} after MATCH is the actual array, but N(IF({1} is needed to preserve the array, otherwise INDEX will only see and accept the first element of the array instead of both elements.

The idea behind +{0,1} is that it returns both the row where the match is found and the row below it as an array, which subsequently returns the corresponding pair of items from the INDEX range.
If you tried to break the formula down into smaller part to test it then you might not see it functioning correctly, arrays are often lost if they are not wrapped in a function that can use them.

 

[dimexi]

{1} always returns TRUE as an array, this ties up with the earlier points of discussion. +{0,1} after MATCH is the actual array, but N(IF({1} is needed to preserve the array, otherwise INDEX will only see and accept the first element of the array instead of both elements.

The idea behind +{0,1} is that it returns both the row where the match is found and the row below it as an array, which subsequently returns the corresponding pair of items from the INDEX range.
If you tried to break the formula down into smaller part to test it then you might not see it functioning correctly, arrays are often lost if they are not wrapped in a function that can use them.

Thank you very much for that, I get it now :D.