Using VLOOKUP to find something that is greater than / less than the lookup value

[Futile Crush]

Hey guys, I need a bit of help. I'm trying to make a VLOOKUP which looks at the lookup value, then finds the value in a certain column of a table which is greater than or equal to that number, and returns a specified result within that row. My attempts have thus far been unsuccessful. Without VBA, how can I achieve this?

 

[Gerald Higgins]

I don't think you can do this with basic VLOOKUP.
Try using MATCH, with -1 as the Match type for greater than or equal to.
Then, add in an INDEX function for the lookup.
For example

=INDEX(E1:F3,MATCH(A1,E1:E3,-1),2)

The lookup range must be sorted in descending order for this to work.

 

[EB08]

I like the above; but depending on what kind of list we're looking at here, using 1 or true as the last argument in the vlookup could achieve what you're after. For example: If you would like to take percentages and turn them into letter grades; you're lookup table would be something like...

A B

0 F
.6 D
.7 C
.8 B
.9 A

your formula would be =vlookup(grade%, A1:B5, 2, 1); anything from 0 to .599 etc would return F; .6 to .6999 etc would return D and so forth.

 

[dms37]

Hi there,

I think this is what you're asking for:

=VLOOKUP(A1,B1:G100,4,TRUE)

Obviuosly change the A1, B1:G100 and the 4 to wherever the value you reference, your range and the column that holds the required info.

Dave

 

[Gerald Higgins]

Dave - I don't agree.

With this dataset your formula returns the next LOWEST value, not next highest as requested.
1...A
2...B
3...C
5...D
when lookup value is 4, i.e. it returns C, matching the 3, but according to the OP we want D, for matching the 5.

If you sort it into descending order, you then get a weird result.

The INDEX/MATCH solution returns D.

 

[dms37]

Gerald,

Quite correct, I should have checked before I posted it - my bad.

What's an OP by the way?

Dave

 

[Gerald Higgins]

Original Post

 

[Futile Crush]

Thanks guys! I tried Match and then got it to work . Turns out that was all I needed, with minus one as the last specifier.

 

[abhijeetms]

May be you should try lookup instead of vlookup. That should give you the value you are looking for.