VLookup Issue - Help!!

Hi All,

I have done a few Vlookups in my time but haven't been able to get this to work. Not sure if vlookup is the correct formula either.
I have attached an example of the report.

On Sheet1 I am trying to add a vlookup into the 'Shipment#' column to lookup the Cust Ref. Value e.g. 813912620 against the data on the second tab 'Shipment Listing Report'.

So I want to lookup 813912620 in the second tab in Column A and return the Shipment Number in Column B.

The issue I am having is that there are multiple Cust Ref. in one cell that the formula is trying to look through and as it is not an exact match it cannot find the value.

Is there a way I can get the vlookup to look into the Cell and search all of the values and if found return the shipment number in column B?

Hope this makes sense.

Appreciate your help. Thanks


Regards
Paul

You can use wildcard characters with VLOOKUP, so you can put this in K5 of Sheet1:

=IFERROR(VLOOKUP("*"&J5&"*",'Shipment Listing Report'!A:B,2,0),"")

then copy down.

Hope this helps.

Pete

This has worked. Thanks a lot.

I can use this however don't understand what you have done to replicate it in future.

Could you do me a favor and explain what these mean? Thanks
=IFERROR
"*"&J5&"*"

The text values that you are looking through could be regarded as:

"some_or_no_text" & "your_lookup_value" & "some_or_no_text"

By putting asterisks (wildcard characters, meaning any or no text) either side of the J5, it enables the lookup value to be located.

However, it might be that the value that you are looking for is not in the list on the other sheet, so by wrapping IFERROR( ... ,"") around the VLOOKUP function this returns a blank instead of the #N/A error that you would get otherwise.

Hope this helps.

Pete

P.S. If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Hi Pete,

Sorry one more question. When I apply this to the full sheet it is only picking up some of the shipment numbers.
I have attached the report.

Do you know what the issue is?

Regards
Paul

I believe it might be due to your formatting, numbers stored as text and all that.

trying to think of an easy work around, but drawing a blank for this situation. I tried copy pasting as values, but it distorted your data.

You can change the formula in K8 to this:

=IFERROR(VLOOKUP("*"&J8&"*",Sheet2!A:B,2,0),"x")

and copy down - this will give you an x where the ref number is not found in Sheet2 (bit easier to see). However, you still get some returns showing zero (e.g. rows 139 to 141), and this is because the data in Sheet2 is inconsistent - the corresponding cell in column B is blank (which Excel puts as zero).

You can put this formula in Q139, for example:

=MATCH("*"&J139&"*",Sheet2!A:A,0)

and copy that down a few rows - it will tell you the row number on Sheet2 where the data can be found (in this case rows 454, 455 and 458 for those 3 zero values), and if you look at those rows in Sheet2 you will find that there is no entry in column B. Instead of having multiple entries in column A and a single entry in B, you have individual entries in cells A453 to A489, and only cell B453 has the relevant Shipment # for that customer ref. This starts much higher up in Sheet2, for example cells A15 to A56 should all be combined into one cell, and so it could be a massive job to get them into the same consistent format.

However, the following formula can overcome the inconsistent layout - put this in K8:

=IFERROR(IF(VLOOKUP("*"&J8&"*",Sheet2!A:B,2,0)="",LOOKUP("zzzz",INDIRECT("sheet2!b1:B"&MATCH("*"&J8&"*",Sheet2!A:A,0))),VLOOKUP("*"&J8&"*",Sheet2!A:B,2,0)),"x")

and copy this down. This uses the VLOOKUP value if it is not blank (i.e. consistent layout), but if it is blank then it will return the previous non-blank cell from column B relative to where a match is found, so you don't have to make all those changes to Sheet2. You can change the "x" back to "" if you prefer.

Hope this helps.

Pete