vlookup

[Richard V]

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.

 

[Mike H]

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

 

[JBeaucaire]

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.

 

[JBeaucaire]

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)))

 

[Shane Devenshire]

Hi,

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