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

#### Futile Crush

##### New Member
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?

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### Gerald Higgins

##### Well-known Member
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
Code:
``=INDEX(E1:F3,MATCH(A1,E1:E3,-1),2)``
The lookup range must be sorted in descending order for this to work.

#### EB08

##### Active Member
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

##### Board Regular
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

##### Well-known Member

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

##### Board Regular
Gerald,

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

What's an OP by the way?

Dave

Original Post

#### Futile Crush

##### New Member
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

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

Replies
3
Views
263
Replies
6
Views
168
Replies
3
Views
182
Replies
8
Views
120
Replies
2
Views
56