-1

this is my first post so i am sorry if this is confusing at all. I am trying to use a vLookup to run a comparative analysis between two reports. I am using a part number as a reference and trying to return the cost associated with the part from one of the two reports. So, the first issue that I encountered was due to the fact that some of the part numbers had letters in them and some didn't, so to be consistent I used the following code to clean up the part numbers:

IFERROR(VALUE(F11&C11), F11&C11)

where F11 and C11 are two components of the part number that needed to be concatenated to generate the full number. Now, the vLookup will not return anything except for #N/A for a few of the part numbers that are actually in the sheet. All of the part numbers are formatted the same for the 892 part numbers that I am searching for but get a returned value on 571 of the 892 part numbers but of the remaining 321 part numbers that did not have a return, about a third actually exist in my sheet. Lastly and for example, part number 110874402 exists in both sheets but gets a #N/A from the vLookup. When I copy the value from one sheet and search it in the other sheet using Ctrl + F, I get the following:

(I have an image to show but apparently can't post it without a reputation of 10 or more...oops)

The highlighted cell shows that the value exists but Excel can't find it. Does anyone have any ideas why this is or what I could be doing differently? I've been having this issue for a few months now on separate projects and haven't found any resolution.

Thanks in advance,

Community
  • 1
  • 1
gatechuks
  • 1
  • 1
  • 1
  • 2
  • 1
    What is your VLookup syntax? – Joe Nov 06 '13 at 17:29
  • Since you can't post images, please give more detail in your question as it's currently difficult to ascertain the situation... – ARich Nov 06 '13 at 17:47
  • =VLOOKUP(F569, BOBJ!$D$3:$P$2237, 7, FALSE) – gatechuks Nov 06 '13 at 18:18
  • I am fairly certain that I am using the function correctly. I am using it the same way that my team and I have used it in other worksheets and utilities. The first value, F569, is the part number from the first worksheet, and the second area is the range in which it is looking into the second worksheet. The "D" column is the column in the second worksheet that has the part number and the 7th column in that range is where the cost data that I want to return exists. Like I said before, it works for most of the part numbers but some of them cannot be found even though they are in both sheets. – gatechuks Nov 06 '13 at 18:23

1 Answers1

0

try =VLOOKUP("*"&TRIM(F569)&"*", BOBJ!$D$3:$P$2237, 7, FALSE) - I have a feeling spaces may have crept around the part numbers, which means that the exact match will not work.

The TRIM takes the spaces from the cell you are looking at, and the "*"'s will allow a wildcard search - note that this also means that CAT would also match CAT1, but if it produces results where there were none before, it gives you something to check for.

SeanC
  • 15,695
  • 5
  • 45
  • 66
  • I was actually using Trim on the entire "F" column at an earlier part in my code but some of my part numbers have "0's" as the first digit but Trim was removing them. I thought that Trim was supposed to only remove spaces but while debugging my code I realized that Trim was removing them. – gatechuks Nov 07 '13 at 15:54