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

Please include the forumla you are currently using.

Also, take a look at this explaination of VLOOKUP:
http://www.contextures.com/xlFunctions02.html

--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott


"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

Cell R14 =+ROUND((I14/$I$91),2) Returns value .04
Cell S14 =LOOKUP(R14,'[Job Size Multipliers
Table.xlsx]Sheet1'!$A$1:$A$10000,'[Job Size Multipliers
Table.xlsx]Sheet1'!$B$1:$B$100000)
Returns value 2.405 but works on .01; .02;.03 and some others.
Job Size Multipliers Table - partial table.
0.01 3.163
0.02 2.660
0.03 2.405
0.04 2.238
I also tried the vlookup
=VLOOKUP(R16,'[Job Size Multipliers Table.xlsx]Sheet1'!$A$1:$B$10000,2)
--
Ruthie


"porter444" wrote:

Please include the forumla you are currently using.

Also, take a look at this explaination of VLOOKUP:
http://www.contextures.com/xlFunctions02.html

--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott


"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

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

--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott


"Ruth" wrote:

Cell R14 =+ROUND((I14/$I$91),2) Returns value .04
Cell S14 =LOOKUP(R14,'[Job Size Multipliers
Table.xlsx]Sheet1'!$A$1:$A$10000,'[Job Size Multipliers
Table.xlsx]Sheet1'!$B$1:$B$100000)
Returns value 2.405 but works on .01; .02;.03 and some others.
Job Size Multipliers Table - partial table.
0.01 3.163
0.02 2.660
0.03 2.405
0.04 2.238
I also tried the vlookup
=VLOOKUP(R16,'[Job Size Multipliers Table.xlsx]Sheet1'!$A$1:$B$10000,2)
--
Ruthie


"porter444" wrote:

Please include the forumla you are currently using.

Also, take a look at this explaination of VLOOKUP:
http://www.contextures.com/xlFunctions02.html

--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott


"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

Try using the VLOOKUP formula but set the 4th argument to 0:

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

That means you want an exact match.

--
Biff
Microsoft Excel MVP


"Ruth" wrote in message
...
Cell R14 =+ROUND((I14/$I$91),2) Returns value .04
Cell S14 =LOOKUP(R14,'[Job Size Multipliers
Table.xlsx]Sheet1'!$A$1:$A$10000,'[Job Size Multipliers
Table.xlsx]Sheet1'!$B$1:$B$100000)
Returns value 2.405 but works on .01; .02;.03 and some others.
Job Size Multipliers Table - partial table.
0.01 3.163
0.02 2.660
0.03 2.405
0.04 2.238
I also tried the vlookup
=VLOOKUP(R16,'[Job Size Multipliers Table.xlsx]Sheet1'!$A$1:$B$10000,2)
--
Ruthie


"porter444" wrote:

Please include the forumla you are currently using.

Also, take a look at this explaination of VLOOKUP:
http://www.contextures.com/xlFunctions02.html

--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott


"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

The return is #N/A which means an exact match isn't found, correct? But I
don't understand why?
--
Ruthie


"T. Valko" wrote:

Try using the VLOOKUP formula but set the 4th argument to 0:

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

That means you want an exact match.

--
Biff
Microsoft Excel MVP


"Ruth" wrote in message
...
Cell R14 =+ROUND((I14/$I$91),2) Returns value .04
Cell S14 =LOOKUP(R14,'[Job Size Multipliers
Table.xlsx]Sheet1'!$A$1:$A$10000,'[Job Size Multipliers
Table.xlsx]Sheet1'!$B$1:$B$100000)
Returns value 2.405 but works on .01; .02;.03 and some others.
Job Size Multipliers Table - partial table.
0.01 3.163
0.02 2.660
0.03 2.405
0.04 2.238
I also tried the vlookup
=VLOOKUP(R16,'[Job Size Multipliers Table.xlsx]Sheet1'!$A$1:$B$10000,2)
--
Ruthie


"porter444" wrote:

Please include the forumla you are currently using.

Also, take a look at this explaination of VLOOKUP:
http://www.contextures.com/xlFunctions02.html

--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott


"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

Same result.
--
Ruthie


"porter444" wrote:

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

--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott


"Ruth" wrote:

Cell R14 =+ROUND((I14/$I$91),2) Returns value .04
Cell S14 =LOOKUP(R14,'[Job Size Multipliers
Table.xlsx]Sheet1'!$A$1:$A$10000,'[Job Size Multipliers
Table.xlsx]Sheet1'!$B$1:$B$100000)
Returns value 2.405 but works on .01; .02;.03 and some others.
Job Size Multipliers Table - partial table.
0.01 3.163
0.02 2.660
0.03 2.405
0.04 2.238
I also tried the vlookup
=VLOOKUP(R16,'[Job Size Multipliers Table.xlsx]Sheet1'!$A$1:$B$10000,2)
--
Ruthie


"porter444" wrote:

Please include the forumla you are currently using.

Also, take a look at this explaination of VLOOKUP:
http://www.contextures.com/xlFunctions02.html

--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott


"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

Ok, that means there's a problem with the numbers in your table.

In the first column of your table you have:

0.01
0.02
0.03
0.04

Are these numbers calculated and the result of a formula?

If cell R14 = 0.04 as you state, compare that value to the 0.04 value in
your table.

=R14=cell in table that is 0.04

Do you get a result of TRUE or FALSE?

What result do you get with this formula:

=(R14-cell in table that is 0.04)=0

--
Biff
Microsoft Excel MVP


"Ruth" wrote in message
...
The return is #N/A which means an exact match isn't found, correct? But I
don't understand why?
--
Ruthie


"T. Valko" wrote:

Try using the VLOOKUP formula but set the 4th argument to 0:

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

That means you want an exact match.

--
Biff
Microsoft Excel MVP


"Ruth" wrote in message
...
Cell R14 =+ROUND((I14/$I$91),2) Returns value .04
Cell S14 =LOOKUP(R14,'[Job Size Multipliers
Table.xlsx]Sheet1'!$A$1:$A$10000,'[Job Size Multipliers
Table.xlsx]Sheet1'!$B$1:$B$100000)
Returns value 2.405 but works on .01; .02;.03 and some others.
Job Size Multipliers Table - partial table.
0.01 3.163
0.02 2.660
0.03 2.405
0.04 2.238
I also tried the vlookup
=VLOOKUP(R16,'[Job Size Multipliers Table.xlsx]Sheet1'!$A$1:$B$10000,2)
--
Ruthie


"porter444" wrote:

Please include the forumla you are currently using.

Also, take a look at this explaination of VLOOKUP:
http://www.contextures.com/xlFunctions02.html

--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott


"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

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

The first column in the table are just manually entered. The result to your
suggested formula is FALSE. Cell R14 is a formula: =+ROUND((I14/$I$91),2)
--
Ruthie


"T. Valko" wrote:

Ok, that means there's a problem with the numbers in your table.

In the first column of your table you have:

0.01
0.02
0.03
0.04

Are these numbers calculated and the result of a formula?

If cell R14 = 0.04 as you state, compare that value to the 0.04 value in
your table.

=R14=cell in table that is 0.04

Do you get a result of TRUE or FALSE?

What result do you get with this formula:

=(R14-cell in table that is 0.04)=0

--
Biff
Microsoft Excel MVP


"Ruth" wrote in message
...
The return is #N/A which means an exact match isn't found, correct? But I
don't understand why?
--
Ruthie


"T. Valko" wrote:

Try using the VLOOKUP formula but set the 4th argument to 0:

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

That means you want an exact match.

--
Biff
Microsoft Excel MVP


"Ruth" wrote in message
...
Cell R14 =+ROUND((I14/$I$91),2) Returns value .04
Cell S14 =LOOKUP(R14,'[Job Size Multipliers
Table.xlsx]Sheet1'!$A$1:$A$10000,'[Job Size Multipliers
Table.xlsx]Sheet1'!$B$1:$B$100000)
Returns value 2.405 but works on .01; .02;.03 and some others.
Job Size Multipliers Table - partial table.
0.01 3.163
0.02 2.660
0.03 2.405
0.04 2.238
I also tried the vlookup
=VLOOKUP(R16,'[Job Size Multipliers Table.xlsx]Sheet1'!$A$1:$B$10000,2)
--
Ruthie


"porter444" wrote:

Please include the forumla you are currently using.

Also, take a look at this explaination of VLOOKUP:
http://www.contextures.com/xlFunctions02.html

--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott


"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

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

Well, at this point I'm out of suggestions and would need to see the file to
figure it out.

--
Biff
Microsoft Excel MVP


"Ruth" wrote in message
...
The first column in the table are just manually entered. The result to
your
suggested formula is FALSE. Cell R14 is a formula: =+ROUND((I14/$I$91),2)
--
Ruthie


"T. Valko" wrote:

Ok, that means there's a problem with the numbers in your table.

In the first column of your table you have:

0.01
0.02
0.03
0.04

Are these numbers calculated and the result of a formula?

If cell R14 = 0.04 as you state, compare that value to the 0.04 value in
your table.

=R14=cell in table that is 0.04

Do you get a result of TRUE or FALSE?

What result do you get with this formula:

=(R14-cell in table that is 0.04)=0

--
Biff
Microsoft Excel MVP


"Ruth" wrote in message
...
The return is #N/A which means an exact match isn't found, correct?
But I
don't understand why?
--
Ruthie


"T. Valko" wrote:

Try using the VLOOKUP formula but set the 4th argument to 0:

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

That means you want an exact match.

--
Biff
Microsoft Excel MVP


"Ruth" wrote in message
...
Cell R14 =+ROUND((I14/$I$91),2) Returns value .04
Cell S14 =LOOKUP(R14,'[Job Size Multipliers
Table.xlsx]Sheet1'!$A$1:$A$10000,'[Job Size Multipliers
Table.xlsx]Sheet1'!$B$1:$B$100000)
Returns value 2.405 but works on .01; .02;.03 and some others.
Job Size Multipliers Table - partial table.
0.01 3.163
0.02 2.660
0.03 2.405
0.04 2.238
I also tried the vlookup
=VLOOKUP(R16,'[Job Size Multipliers
Table.xlsx]Sheet1'!$A$1:$B$10000,2)
--
Ruthie


"porter444" wrote:

Please include the forumla you are currently using.

Also, take a look at this explaination of VLOOKUP:
http://www.contextures.com/xlFunctions02.html

--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott


"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

Thank you for all your suggestions!
--
Ruthie


"T. Valko" wrote:

Well, at this point I'm out of suggestions and would need to see the file to
figure it out.

--
Biff
Microsoft Excel MVP


"Ruth" wrote in message
...
The first column in the table are just manually entered. The result to
your
suggested formula is FALSE. Cell R14 is a formula: =+ROUND((I14/$I$91),2)
--
Ruthie


"T. Valko" wrote:

Ok, that means there's a problem with the numbers in your table.

In the first column of your table you have:

0.01
0.02
0.03
0.04

Are these numbers calculated and the result of a formula?

If cell R14 = 0.04 as you state, compare that value to the 0.04 value in
your table.

=R14=cell in table that is 0.04

Do you get a result of TRUE or FALSE?

What result do you get with this formula:

=(R14-cell in table that is 0.04)=0

--
Biff
Microsoft Excel MVP


"Ruth" wrote in message
...
The return is #N/A which means an exact match isn't found, correct?
But I
don't understand why?
--
Ruthie


"T. Valko" wrote:

Try using the VLOOKUP formula but set the 4th argument to 0:

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

That means you want an exact match.

--
Biff
Microsoft Excel MVP


"Ruth" wrote in message
...
Cell R14 =+ROUND((I14/$I$91),2) Returns value .04
Cell S14 =LOOKUP(R14,'[Job Size Multipliers
Table.xlsx]Sheet1'!$A$1:$A$10000,'[Job Size Multipliers
Table.xlsx]Sheet1'!$B$1:$B$100000)
Returns value 2.405 but works on .01; .02;.03 and some others.
Job Size Multipliers Table - partial table.
0.01 3.163
0.02 2.660
0.03 2.405
0.04 2.238
I also tried the vlookup
=VLOOKUP(R16,'[Job Size Multipliers
Table.xlsx]Sheet1'!$A$1:$B$10000,2)
--
Ruthie


"porter444" wrote:

Please include the forumla you are currently using.

Also, take a look at this explaination of VLOOKUP:
http://www.contextures.com/xlFunctions02.html

--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott


"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