vlookup and lookup

R

Ruth

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?
 
R

Ruth

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

porter444

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 said:
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 said:
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
Click to expand...
Click to expand...
 
T

T. Valko

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 said:
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 said:
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
Click to expand...
Click to expand...
 
R

Ruth

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


T. Valko said:
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 said:
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 said:
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


:

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?
Click to expand...
Click to expand...
Click to expand...
 
R

Ruth

Same result.
--
Ruthie


porter444 said:
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 said:
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 said:
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


:

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?
Click to expand...
Click to expand...
Click to expand...
 
T

T. Valko

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 said:
The return is #N/A which means an exact match isn't found, correct? But I
don't understand why?
--
Ruthie


T. Valko said:
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 said:
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


:

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


:

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?
Click to expand...
Click to expand...
Click to expand...
 
S

Shane Devenshire

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
 
R

Ruth

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 said:
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 said:
The return is #N/A which means an exact match isn't found, correct? But I
don't understand why?
--
Ruthie


T. Valko said:
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


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


:

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


:

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?
Click to expand...
Click to expand...
Click to expand...
 
R

Ruth

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 said:
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 said:
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?
Click to expand...
Click to expand...
 
T

T. Valko

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 said:
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 said:
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 said:
The return is #N/A which means an exact match isn't found, correct?
But I
don't understand why?
--
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


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


:

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


:

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?
Click to expand...
Click to expand...
Click to expand...
 
R

Ruth

Thank you for all your suggestions!
--
Ruthie


T. Valko said:
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 said:
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 said:
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


The return is #N/A which means an exact match isn't found, correct?
But I
don't understand why?
--
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


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


:

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


:

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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

vlookup - find an alternative approximate match 4
Lookup function 7
Lookup 3
Trapping errors in a VLOOKUP function 1
Vlookup & Find 2
CONCATENATE & LOOKUP Functions 0
Vlookup and return sheet name also 2
Excel 2007 LOOKUP Problem? 3

Top