Excel XLOOKUP - find and return data containing text, but exclude if it includes other text-CodePudding

I have xlookup function searching range F1:F44 for the text in F2 and then returning the match:

XLOOKUP(TRUE,ISNUMBER(FIND(F2,F1:F44)),F1:F44,,2)

However I need to exclude entries if they include text listed in array A1:A10.

I have tried switching over to something like this with the NOT, but always get an error:

=XLOOKUP(TRUE,(ISNUMBER(FIND(F2,F1:F44))*NOT(A1:A10),F1:F44))

I also tried

=XLOOKUP(TRUE,ISNUMBER(FIND(F2,F1:F44)*NOT(FIND(A1,F1:F44))),F1:F44,"no match",2)

but in this case the result being returned contains the value in A1 that is supposed to be excluded with the "NOT".

Tried applying this suggestion:

=XLOOKUP(TRUE,ISNUMBER(FIND(F2,DumpSample!F1:F44))*(MMULT(--ISNUMBER(FIND(TRANSPOSE(exclude!A1:A8),DumpSample!F1:F44)),SEQUENCE(ROWS(exclude!A1:A8)))=0),DumpSample!F1:F44,"no match",2)

But only getting "no match". Nothing returned from the dataset based on the term it should find in F2, which is typically a number, e.g. 531

The data it is pulling from is a list F1:F44 that contains entries such as: MyText(531) or Gap/MyText(531)

The value of A1 in the exclude list is "Gap".

So I would be looking for it to return MyText(531) but not Gap/MyText(531)

Updated to "search" instead of "find" and still no match:

=XLOOKUP(TRUE,ISNUMBER(SEARCH(F2,DumpSample!F1:F44))*(MMULT(--ISNUMBER(FIND(TRANSPOSE(exclude!A1:A8),DumpSample!F1:F44)),SEQUENCE(ROWS(exclude!A1:A8)))=0),DumpSample!F1:F44,"no match",2)

CodePudding user response：

use

=XLOOKUP(1,ISNUMBER(FIND(F2,F1:F44))*(MMULT(--ISNUMBER(FIND(TRANSPOSE(A1:A10),F1:F44)),SEQUENCE(ROWS(A1:A10)))=0),F1:F44,"no match",2)

The MMULT()=0 will return an array of TRUE/FALSE. TRUE if none of the values in A1:A10 are found in the cell, FALSE if found

Also where multiplying two Boolean we get either 1 or 0 so instead of TRUE we need to look for 1