Look up the Nth value in a column (for instance the 2nd number)

[bartstaal98]

Heya people,

Been struggling with this formula. I want to calculate something which will give me a number. I'm trying to make a formula that checks this number and then looks up in a column with numbers what the value is that matches this number.

Note: when the result is 2 I don't need to have it find the number 2 specifically, but the 2nd number of the column, and then return what that number is.

To complicate things, the result may also be 3.5 or 3.75 or something. This would mean 4th number - 3rd number * 0.5 + 3rd number.

Attached is a sample sheet. For any additional info let me know, kinda hard to explain what I mean haha.

[Limor_OP]

Hi,
Try this in E4:
=(INDEX($I$2:$I$13,ROUNDUP(E3,0))+INDEX($I$2:$I$13,ROUNDDOWN(E3,0)))/2

[Limor_OP]

Or:
=(index($i$2:$i$13,ceiling(e8,1))+index($i$2:$i$13,floor(e8,1)))/2

[bartstaal98]

Thanks! This works for any whole value yes! Do you by any chance also know how to tweak it to calculate 3,25, 3,5 and 3,75 etc?

[Limor_OP]

It does calculate fractions.
Please see attached.

[bartstaal98]

3.75 should return 4,5 though.

- 3.75 is between the 3rd and 4th value
- that means: (((4th value - 3rd value) * 0.75) + 3rd value)

- 6.25 is between the 6th and 7th value
- that means: (((7th value - 6th value) * 0.25) + 6th value)

Not sure if this is possible because you'd need to know what the decimals are in the 'find' number

[Limor_OP]

Try this in E9:
=((INDEX($I$2:$I$13,ROUNDUP(E8,0))-INDEX($I$2:$I$13,ROUNDDOWN(E8,0))))*($E8-INT($E8))+INDEX($I$2:$I$13,ROUNDDOWN(E8,0))