Taking number in between for interpolation

[hamidun]

Hi all,

I need help to take number in between cells. For example, I have numbers 1, 2, 3, 4, 5, 6, 7. When I type 5 in a column, I want to have 4 and 6, above and below 5. I am doing this to ease interpolation, so that whenever I want to do interpolation, I just need to type what number, I would like to have to be interpolated.

Thanks for the help.

Attached file is the example, it may help and may not help.

[azumi]

Perhaps:

In D9:
=INDEX(A2:A7;MATCH(D10;A2:A7;1))

In D11:
=INDEX(A2:A7;MATCH(D10;A2:A7;1)+1)

[Ace_XL]

In D9
=IFERROR(INDEX(A$2:A$7,MATCH($D$10,$A$2:$A$7,0)),INDEX(A$2:A$7,MATCH($D$10,$A$2:$A$7,1)))

In D11
=IFERROR(INDEX(A$2:A$7,MATCH($D$10,$A$2:$A$7,0)),INDEX(A$2:A$7,MATCH($D$10,$A$2:$A$7,1)+1))

In E9
=INDEX(B$2:B$7,MATCH($D9,$A$2:$A$7,0))
Copy down and across

[hamidun]

In D9:
=INDEX(A2:A7;MATCH(D10;A2:A7;1))
In D11:
=INDEX(A2:A7;MATCH(D10;A2:A7;1)+1)

This formulas work perfectly, thanks!!!

In D9
=IFERROR(INDEX(A$2:A$7,MATCH($D$10,$A$2:$A$7,0)),INDEX(A$2:A$7,MATCH($D$10,$A$2:$A$7,1)))
In D11
=IFERROR(INDEX(A$2:A$7,MATCH($D$10,$A$2:$A$7,0)),INDEX(A$2:A$7,MATCH($D$10,$A$2:$A$7,1)+1))
In E9
=INDEX(B$2:B$7,MATCH($D9,$A$2:$A$7,0))

These also work perfectly, with bonus formula that really really helps!!!

Thanks all!!

[azumi]

Glad it works

[hamidun]

But for Ace_XL formula, when I put number that is the same with number in the data, the interpolation doesn't work. I don't know what is wrong with your formula. In the interpolation cell shows #DIV/0!