CODEWITHSUNDEEP
excelexcel-formulaxlookup
Deke
Deke

Reputation: 495

XLOOKUP to search wildcards between two columns

I need some help with my XLOOKUP. I'm also not sure if I can do this but based on my reading it seems possible.

I have 2 spreadsheets the first is my ChangeMaster which has all my changes I need to verify and the PackProfile sheet which is where I need to verify and update if needed.

Here is what I have so far but it isn't quite right

=XLOOKUP(C2,'[MasterChangenotice 1-17.xlsx]ChangeMaster_220116'!$C$2:$C$466,'[MasterChangenotice 1-17.xlsx]ChangeMaster_220116'!$M$2:$M$466,XLOOKUP(N2&"*",'[MasterChangenotice 1-17.xlsx]ChangeMaster_220116'!$M$2:$M$466,'[MasterChangenotice 1-17.xlsx]ChangeMaster_220116'!$M$2:$M$466,0,2))

This is my change master file. I'm trying to validate that the unique number in Column C and the Value in Column M are within my pack profile sheet...

ChangeMaster

... and line up with the unique number in Column C and the highlighted value in my PackProfile sheet below.

PackProfile

Hopefully I'm explaining this correctly but if any more info is needed I'll be happy to provide it. Any help is greatly appreciated!

Upvotes: 0

Views: 276

Answers (1)

rayleone
rayleone

Reputation: 758

In your ChangeMaster spreadsheet you could create a Validation column to enter the following:

=ISNUMBER(SEARCH(M2,XLOOKUP(C2,[PackProfile.xlsx]Sheet1!$C:$C,[PackProfile.xlsx]Sheet1!$N:$N)))

The above should generate TRUE and FALSE responses from which you could have additional functions triggered.

Upvotes: 1

Related Questions