XLOOKUP when the lookup value has comma separated cell

Hello,

I have a spreadsheet where Column A in Sheet1 has a list of IDs that are separated by commas in a cell. In Sheet2, Column A has a list of unique IDs (not comma separated) that match Column A in Sheet 1. I would like to know how I can use XLOOKUP to find a match from within a comma separated cell in Column A of Sheet1, and once a match is found, to bring the value from Column B in Sheet2 over to Column B in Sheet1? Please see attached an example of the spreadsheet I am referring to.

I tried =XLOOKUP("*"&A2&"*",Sheet2!A:A,Sheet2!B:B,"") but that did not work.

Any ideas would be greatly appreciated.

XLOOKUP is not very familiar yet, you can use another solution, refer to

Worksheet name : Sheet1
Cell B2 formula , Drag down

HTML Code: 

@Eades1412 You're Welcome. Glad to help . Thank You for the feedback


If you finally get a solution please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".
- Please click on the *Add Reputation button at the bottom left.

Hi everyone,

Thank you to wk9128 who initially helped me with the above formula. This worked with what I was doing before, but now I am having trouble using the same formula for a different scenario. I am not sure if it is the formatting, as the numbers I am trying to match have leading zeros in it. Basically, my lookup value is a bunch of IDs that are separated by commas in one cell. I would then like to see if that matches an ID from a separate worksheet, which has leading zeros in it. The formula above, unfortunately did not work. Could it be something to do with the formatting of my numbers? See attached worksheet for an example.

The bold IDs in column A of Sheet1 are the IDs I am trying to match. Sheet2, Column A has the 'WOS:' IDs that I am matching to (I have formatted these using the 'Custom' formatting as they were automatically defaulting to scientific numbers. I would then like to bring across data from Column B, Sheet2 onto Sheet1.

Any ideas where I am going wrong on this occasion?

Thanks in advance!
Sophia

Hi wk9128, what was the solution you provided to stated problem? I am unable to find.

Hi Eades1412, what was the solution please?

Can you explain the use of the 1000 in the formula and what that does?