|
vlookup and lookup
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 |
vlookup and lookup
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 |
vlookup and lookup
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 |
vlookup and lookup
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 |
vlookup and lookup
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 |
vlookup and lookup
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 |
vlookup and lookup
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 |
vlookup and lookup
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 |
vlookup and lookup
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 |
vlookup and lookup
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 |
vlookup and lookup
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 |
vlookup and lookup
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 |
vlookup and lookup
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 |
| All times are GMT +1. The time now is 05:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com