Using SEARCH Function When Looking Into Another Table

Question

I have two tables, Table1 and Table2. Table1 has a list of file names that have been cut off, such as filenameexample.p instead of filenameexample.pdf. Table2 has a list of full file names. I need to do a search to find the match from Table1 in Table2.

I'm using ISNUMBER(SEARCH()), which normally works for me but it's not here and returning FALSE. I've confirmed that there is in fact a match so it should return TRUE.

The actual formula that I'm using in Table1 is =ISNUMBER(SEARCH(B2, Table2[@File Name])). I've never used the @ symbol in any of my equations but Excel was telling me to update my original formula without the @ so I did. If I remove the @, I get a SPILL error.

What am I doing wrong?

Also, I don't think it's relevant but both tables have been adjusted in Power Query.

Answer 1

Hi @Renee Crozier,

Thank you for posting your question in the Microsoft Q&A forum.
Your current formula =ISNUMBER(SEARCH(B2, Table2[@File Name])) is searching for the value in B2 from Table1 within a single row of Table2 because using Table2[@File Name] means you want to at the value in the File Name column in one single current row of table 2.

Since you're trying to search across all of Table2 and the SEARCH function only doesn't handle arrays, we suggest you can try use this formula:

=SUMPRODUCT(--ISNUMBER(SEARCH(B2,Table2[File Name]))) > 0

This formula will check if the value in B2 exists anywhere in the table2[File Name] column and will return TRUE if at least one match exists.

Please understand that our initial response does not always resolve the issue immediately. However, you can try workaround and let us know how it went or if you need help with any steps, we can work together to find a solution.    

Thank you for your understanding and cooperation. I'm looking forward to your reply.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.