vlookup
 

I am looking for a way to use vlookup to find data that is not a match but
will go the the next higher value.

vlookup
 

rRichard,

This isn't clear. Suppose we have the data set

1 8
2 9
5 10
6 11
7 12
8 13

If we look up 5 in the first column what value do you want returned and why
ditto for looking up 3 in the first column

Mike

"Richard V" wrote:

I am looking for a way to use vlookup to find data that is not a match but
will go the the next higher value.

vlookup
 

That's not an option in VLOOKUP, if you use FALSE as the final parameter, it
will find exact matches only, and if you use TRUE it will drop down to the
closest match. There isn't a round UP option.

But you could use an INDEX(MATCH) function and trick it into doing that, but
it might get weird when there IS an exact match.

If the data you want to return is in column B based on a lookup of a value
in column A and you want it to find the closest match and round up, you have
to offset the ranges by one row to trick it into doing that. I'm putting the
value to match in C1

=INDEX($B$2:$B$101,MATCH(C1,$A$1:$A$100,1))

That will do what you want when there ISN'T an exact match. If there is
won't get the right answer. So you probably need to do a test first.

=IF(ISNA(MATCH(C1,$A$1:$A$100,0)),
INDEX($B$2:$B$101,MATCH(C1,$A$1:$A$100,1)),
INDEX($B$1:$B$100,MATCH(C1,$A$1:$A$100,0)))

Hope that gets you closer.

INDEX(MATCH) clarification
 

Sorry, part of my message disappeared. I was saying that if the value you
want to match is an EXACT match in the lookup column, it would not give you
the correct answer, so the second longer formula should be used all the time
since it checks for an exact match before choosing which formula to use.

=IF(ISNA(MATCH(C1,$A$1:$A$100,0)),
INDEX($B$2:$B$101,MATCH(C1,$A$1:$A$100,1)),
INDEX($B$1:$B$100,MATCH(C1,$A$1:$A$100,0)))

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.

vlookup
 

Hi,

Why don't you show us what your data looks like and what result you expect.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Richard V" wrote:

I am looking for a way to use vlookup to find data that is not a match but
will go the the next higher value.