Find Function with List / Use OR Function to Find

[nickyd8855]

I am trying to use the FIND function to search through text within a list (Column D) and every time there is an exact match with any text from that list I want the entire string of the text to be returned in Column C. Currently my formula will only work for the first cell of the list that I am trying to search through and will not search through an entire list. As you can see the text in C2 is pulled in properly, but that is only because cell B2 has -AA- in the text. If I change the contents in cell B2 from -AA- to -AF- it will not return the text anymore. Is there a way to change my formula so that it searches the entire list in Column D and every time it finds a match from the cell in B it will return the entire string in the cell next to it in Column C?

Here is the formula I am using: =IFERROR(TRIM(MID(SUBSTITUTE($B2," ",REPT(" ",99)),MAX(1,FIND(D2:D4,SUBSTITUTE($B2," ",REPT(" ",99)))-50),99)),"NO SITE PLAN")

2018-12-10_9-20-47.png

[6StringJazzer]

Welcome to the Forum nickyd8855!

This part of your formula

FIND(D2:D4

will not work the way you think it will. It will not run through all the cells to check for a match. It will only check a single value; when you give it a range like that it will only use the upper-left cell in the range.

However, I think this will work if you enter your same formula as an array formula. First change D2:D4 to D$2:D$4 (or whatever row number you want to use). Select C2, hit F2, then press CTRL+SHIFT+ENTER to enter the array formula. You have done it correctly if the formula in the formula box has {braces} around it. You cannot type in the braces; they are just an indicator that it is an array formula. Then copy down to all rows.

If this does not work then please attach your Excel file. An image attachment has limited value. Attaching the Excel file is easier than taking a screenshot first and then attaching that. The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

[nickyd8855]

Hey Jeff, I tried the CTRL + SHFT + ENTER to turn it into an array but I didn't have any luck. Can you take a look at the attached Excel file to see if you are able to get it to work. Also, this is just a small subset of the 150K rows that I wanted to have this applied to. So please let me know if you need all of the actual data. Thanks Jeff I appreciate your assistance!

[6StringJazzer]

Yeah, I tried something like this on a small sample and didn't get it to work. I believe is it possible with a somewhat more complex formula. I (or someone else) will have a look.

[Bo_Ry]

Please try at C2 and copy down

=IF(COUNT(INDEX(FIND($D$2:$D$8,B2),)),TRIM(LEFT(SUBSTITUTE(MID(B2,FIND("_",B2)+3,50)," ",REPT(" ",50)),50)),"NO SITE PLAN")