VLookup is not working correctly
I am looking up an on-hand quantity from one table to populate another table
based on an item number. I have a couple of scenarios where VLookup is bringing back the same value for both items, but when I look them up manually, I find that the two items have a different on-hand value. For item number UX2022U and UX2022U* I get the same on-hand returned....smae thing goes for item numbers UX2022R and UX2022R*. The vlookup I am using is this: =VLOOKUP(A828,'[ilicst 16-jul-08.xls]Sheet1'!$A$2:$I$10189,8,FALSE) Doesn't the "FALSE" in the expression tell Excel to find an EXACT match ? I think the '*' in the last position of my item number is causing Excel to treat that like a wildcard and not find the EXACT match. Any ideas on how to work around this ? Thanks ! |
VLookup is not working correctly
On Thu, 17 Jul 2008 13:51:05 -0700, Eric @ BP-EVV
wrote: I am looking up an on-hand quantity from one table to populate another table based on an item number. I have a couple of scenarios where VLookup is bringing back the same value for both items, but when I look them up manually, I find that the two items have a different on-hand value. For item number UX2022U and UX2022U* I get the same on-hand returned....smae thing goes for item numbers UX2022R and UX2022R*. The vlookup I am using is this: =VLOOKUP(A828,'[ilicst 16-jul-08.xls]Sheet1'!$A$2:$I$10189,8,FALSE) Doesn't the "FALSE" in the expression tell Excel to find an EXACT match ? I think the '*' in the last position of my item number is causing Excel to treat that like a wildcard and not find the EXACT match. Any ideas on how to work around this ? Thanks ! That is very strange. I've never run across it, but I can confirm it occurs in Excel 2007. And it also occurs with the MATCH worksheet function. It doesn't seem to be documented in HELP. This **array-entered** formula seems to be one work-around: =INDEX('[ilicst 16-jul-08.xls]Sheet1'!$A$2:$A$10189,MATCH( TRUE,EXACT(A10,'[ilicst 16-jul-08.xls]Sheet1'!$H$2:$H$10189),0)) To **array-enter** a formula, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. --ron |
VLookup is not working correctly
I think the '*' in the last position of my item number is
causing Excel to treat that like a wildcard and not find the EXACT match. You are correct. Try it like this: =VLOOKUP(SUBSTITUTE(A828,"*","~*"),'[ilicst 16-jul-08.xls]Sheet1'!$A$2:$I$10189,8,0) In the formula, we "virtually" replace the * with ~*. The ~ tells Excel to treat the * as a literal character and not a wildcard. Note that this is based on your *limited* sample. The sample lookup values you posted are TEXT strings. This approach won't work if your lookup values were numbers because the result of the SUBSTITUTE function is a *TEXT* value. For example: 10 10* The above formula wouldn't be able to find 10 because the SUBSTITUTE function converts it from numeric 10 to text "10". -- Biff Microsoft Excel MVP "Eric @ BP-EVV" wrote in message ... I am looking up an on-hand quantity from one table to populate another table based on an item number. I have a couple of scenarios where VLookup is bringing back the same value for both items, but when I look them up manually, I find that the two items have a different on-hand value. For item number UX2022U and UX2022U* I get the same on-hand returned....smae thing goes for item numbers UX2022R and UX2022R*. The vlookup I am using is this: =VLOOKUP(A828,'[ilicst 16-jul-08.xls]Sheet1'!$A$2:$I$10189,8,FALSE) Doesn't the "FALSE" in the expression tell Excel to find an EXACT match ? I think the '*' in the last position of my item number is causing Excel to treat that like a wildcard and not find the EXACT match. Any ideas on how to work around this ? Thanks ! |
VLookup is not working correctly
Just to add...
My favorite! =VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~"," ~~"),"?","~?"),"*","~*"), Sheet2!$A:$B,2,FALSE) To handle both the * and ? wildcard and the ~ "escape" character. Eric @ BP-EVV wrote: I am looking up an on-hand quantity from one table to populate another table based on an item number. I have a couple of scenarios where VLookup is bringing back the same value for both items, but when I look them up manually, I find that the two items have a different on-hand value. For item number UX2022U and UX2022U* I get the same on-hand returned....smae thing goes for item numbers UX2022R and UX2022R*. The vlookup I am using is this: =VLOOKUP(A828,'[ilicst 16-jul-08.xls]Sheet1'!$A$2:$I$10189,8,FALSE) Doesn't the "FALSE" in the expression tell Excel to find an EXACT match ? I think the '*' in the last position of my item number is causing Excel to treat that like a wildcard and not find the EXACT match. Any ideas on how to work around this ? Thanks ! -- Dave Peterson |
All times are GMT +1. The time now is 02:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com