How Do I Use the Find Function in an Array Formula

Hi! I know how to use the find function in Excel and I also know how to loop through an array in VBA to see if a certain string is present. However, I'd like to learn how to use an array formula within a worksheet to see if a cell's string value is in a value of an array that may contain those unique strings but are also surrounded by other strings. For instance:

Range A1:A10 is my first array and can be visualized as ("Electricity", "Water", "Sewage", etc...)
Range B1:B10, which are in an arbitrary order that does not correlate with the corresponding cell in column A, may look like ("The bill for Water was...","Last month, my electricity bill was...", etc...).

How would I do something along the lines of {=find(A1,B1:B10,1)}? I am trying to see if each individual element of the first array, range A1:A10, appears in the second array, range B1:B10.

As mentioned, this is easy to do in VBA, however, I am just curious as to how this would be done.

Thanks!

Originally Posted by MrShorty

I would tend not to use the FIND() function for this. I would probably use one of Excel's lookup functions with the exact match option. When you choose the exact match option for one of Excel's lookup functions, you can use wildcard characters (like * or ?) to search for a string within the other strings. For the scenario you describe, something like =MATCH(CONCATENATE("*",A1,"*"),$B$1:$B$10,0) will return the row number that contains the text string if it exists. If not, it will return the N/A error.

(If you object to the N/A error value when it is not found, you can nest that inside of an IFERROR() or IFNA() function (or similar) to trap the error and return whatever you would prefer to return.)

Would something like that work for you?

That works amazingly :D Thanks!