Finding the values that don't match from one sheet inside a few cells of another sheet-CodePudding

I have an excel document with two sheets sheet1 and sheet2. Sheet1:

ID number       Name
123             John
321             Pete

Sheet2:

Book             Comment                   Comment2          Comment3
Harry Potter                                                 My ID number is 123
TLOTR            The book is ripped        ID: 321
Dune             Nice book                 ID is 999         None

I would like to search the column "ID number" from Sheet1 inside the text in the columns "Comment", "Comment2" and "Comment3" from Sheet2, and return the whole row from Sheet2 of the IDs that don't match.

So, in this case, the return would be:

Dune             Nice book                 ID is 999         None

I have been trying the vlookup, I know how to search inside a text, and how to search inside multiple columns, but I don't know how to mix both processes

CodePudding user response：

It looks like you have two problems.

your ID or key to link the two spreadsheets together must match the data type. For example, one sheet you have ID number: with a list of only numbers. The other sheet you have text and numbers.
try Xlookup instead.

Both columns you're using as the matching key has to be the same data type (letters or numbers)

First, use Text to columns (in tools, Data - text to columns) to separate your ID numbers from the rest of your text string in your sheet 2 columns that contain the Id numbers.

= Xlookup(CellwithfirstIDnumber,Columnfromsheet2tosearch, Column from sheet2tobring back, "no match")

Good luck