Vlookup approximate match question.

[Guest]

OK if I lookup Jan 5 {vlookup(jan 5, a1:b4, 2)} returns 2 since the function
returns the next largest value that is less than lookup_value. How do I get
the value for the next smallest value that is greater than the lookup_value?
In example below that would be 6 not 2.

Jan 1 2
Jan 7 6
Jan 13 0
Jan 15 8

Thanks.

 

[Bernie Deitrick]

Insert a new cell A1, To get an offset:

Blank 2
Jan 1 6
Jan 7 0
Jan 13 8
Jan 15 8

then use

=VLOOKUP(DATEVALUE("jan 5"), A1:B5, 2)


HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

And add one to all of your dates, except the last one.... sorry.

Blank 2
Jan 2 6
Jan 8 0
Jan 14 8
Jan 15 8

You could also change the blank to an early date... play around....

HTH,
Bernie
MS Excel MVP

 

[Guest]

That would work, but I have a long list. I can make this solution work if I
have to. I guess I was hoping for some sort of change to the formula.

 

[Bernie Deitrick]

Then with your date value of 1/5 in cell D1:

=IF(ISERROR(MATCH(D1,A1:A4,FALSE)),INDEX(B1:B4,MATCH(D1,A1:A4)+1),INDEX(B1:B4,MATCH(D1,A1:A4)))

HTH,
Bernie
MS Excel MVP