Hi,

My wording was just different "the next largest value that is less" which I
copied out of Excel Help but means the same as yours "the next smallest
value", correct? Yours sounds better!

The values in the table are manually entered 2 places to the right of the
decimal so should not need any rounding and should be the exact decimal
places as the lookup value. Here is the formula I have after your suggested
changes =VLOOKUP(ROUNDUP(R16,2),ROUND('[Job Size Multipliers
Table.xlsx]Sheet1'!$A$1:$B$10000,2),2,O). R16 is a formula so needed to
round that also and a prior response to this post advised I use the ROUNDUP
function. This formula returns #NAME?

Thanks,
Ruth
--
Ruthie


"Shane Devenshire" wrote:

Hi,

First, the VLOOKUP and LOOKUP functions do not return the next largest value
when a match is not found, they return the next smallest value. It is
important with approximate matches to sort the lookup table in ascending
order on the first column otherwise your result will in almost all cases be
incorrect.

Second, you get NA error messages when there is no match when using exact
matchs (fourth argument False or 0) if there is no exact match. It looks
like the values in the table need to be rounded to the same number of decimal
places as the lookup value, you can do this in the VLOOKUP or modify the
tables data, here is the formula modification:

=VLOOKUP(R16,ROUND('[Job Size Multipliers
Table.xlsx]Sheet1'!$A$1:$B$10000,2),2,0)

Now the formula must be array entered that means type the formula and press
Shift+Ctrl+Enter rather than just Enter.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"Ruth" wrote:

I'm using a simple 2 column table with either vlookup or lookup function and
some of the return values are the row above. I realize if it doesn't have an
exact match it will return the next largest value that is less so I used the
Round function to make sure there would be exact matches. What am I missing?
--
Ruthie