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

Question

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(1,ISNUMBER(FIND(G2,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)

and it seems to be working.

Scott Craner · Accepted Answer · 2022-08-03T17:55:42.340

1

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

edited Aug 03 '22 at 17:55

answered Aug 03 '22 at 17:14

Scott Craner

148,073
10
49
81

I am getting a too many arguments error with this suggestion – asaysno Aug 03 '22 at 17:18
@asaysno sorry missed a `)` see edit – Scott Craner Aug 03 '22 at 17:21
Problem now is that it is not returning any results based on what it should find from the F2 cell. Just says no match. – asaysno Aug 03 '22 at 17:28
you will need to post some test data and expected output. It is hard to debug without that. – Scott Craner Aug 03 '22 at 17:30
one thing to remember is that `FIND` is case sensitive: `A<>a` If you want case insensitive then use `SEARCH` instead of `FIND` – Scott Craner Aug 03 '22 at 17:31
I posted some sample data, and updated to search instead of find, but still no match – asaysno Aug 03 '22 at 17:37
still returning no match :( – asaysno Aug 03 '22 at 17:52
F2 is in the lookup array, should that be something different. I used E2 (see above) as it is working for me – Scott Craner Aug 03 '22 at 17:55
Not sure why it is not working. It works for me. You may want to check the data. Test a few cells that you think should match and do just the find and just the MMULT on that one cell to see if they return correctly. – Scott Craner Aug 03 '22 at 18:09
You may have spaces or other non printable characters in the list in A1:A10. That would throw it off. – Scott Craner Aug 03 '22 at 18:22

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

1 Answers1