How to lookup values above and below from a table?

[Kimberlily307]

Hi, can someone please help me? Here is what I am trying to do:

I have a table of values:
Field1 Field2
1 45
2 16
3 5

I also have a value to look up, but it is in between the values in the table
What I want is to look up the values above and below and save them in 4 new variables (so I can do a particular kind of extrapolation using the four new variables).

For example, if I lookup 1.5, then I would have:
Field1_below = 1
Field1_above = 2
Field1_below = 45
Field1_above = 16

If I lookup 2.5, I would get
Field1_below = 2
Field1_above = 3
Field1_below = 16
Field1_above = 5

Can this be done with some fancy vlookup function?

Ideally this would also be able to recognize if you picked a value above the table, in which case it would populate the "below" fields with the maximum entry and put some kind of flag in the "above" fields.

Eventually this will need to be a macro but I haven't figured out how to do it in the spreadsheet view yet either. Any help would be greatly appreciated!!

[rylo]

Hi

Assume your reference data is in the range A1:B4
D1: contains your lookup value (1.5)
D2: Field1_below
D3: Field1_above
E2: =VLOOKUP(D1,A2:B4,1)
F2: =VLOOKUP(D1,A2:B4,2)
E3: =VLOOKUP(ROUNDUP(D1,0),A2:B4,1,FALSE)
F3: =VLOOKUP(ROUNDUP(D1,0),A2:B4,2,FALSE)

See how that goes.

rylo

[Kimberlily307]

Thanks rylo,

I guess I shouldn't oversimplify my example... my table doesn't actually have nice round numbers, so simple rounding won't work. The values I am working with look more like this:

0.0115708 0.9889663
0.0124817 0.9881258
0.0133302 0.9873454
0.0133758 0.9873035
0.0144289 0.9863375

[Richard Buttrey]

Hi,

Assuming your data is in A1:B3 and target value in D1 then

=INDEX($A$1:$A$3,MATCH($D$1,$A$1:$A$3),1)
=INDEX($A$1:$A$3,MATCH($D$1,$A$1:$A$3)+1,1)
=INDEX($B$1:$B$3,MATCH($D$1,$A$1:$A$3),1)
=INDEX($B$1:$B$3,MATCH($D$1,$A$1:$A$3)+1,1)

[rylo]

HI

too slow.....


rylo