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

Futile Crush

New Member
Joined
Oct 3, 2009
Messages
45
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? :confused:
 

Some videos you may like

Excel Facts

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

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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
Joined
Jun 9, 2008
Messages
343
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
Joined
Nov 29, 2006
Messages
231
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
Joined
Mar 26, 2007
Messages
9,115

ADVERTISEMENT

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
Joined
Nov 29, 2006
Messages
231
Gerald,

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

What's an OP by the way?

Dave
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115

ADVERTISEMENT

Original Post :)
 

Futile Crush

New Member
Joined
Oct 3, 2009
Messages
45
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
Joined
Nov 21, 2009
Messages
5
May be you should try lookup instead of vlookup. That should give you the value you are looking for.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,566
Messages
5,572,953
Members
412,491
Latest member
tweetytoon
Top