Array to find many

[Rudi]

Hi,

See attached. Question originated at Excel Forum
I need an array formula to find many terms in a column.
TX

[HansV]

Enter the following array formula, confirmed with Ctrl+Shift+Enter, in B2:

=IFERROR(INDEX($C$2:$C$5,MATCH(TRUE,ISNUMBER(FIND($C$2:$C$5,A2)),0)),"")

Fill down to B21.

[Rudi]

Hans,

Is there a process that you follow to string together an array like this? IOW: how do you work out which functions to put together to get the end result. If you can point me in the right direction or share an insightful nugget...it will be great. TX.

[HansV]

I worked from the inside out: I first entered

=FIND($C$2:$C$5,A2)

You'll see either a number or #VALUE!, but if you select FIND($C$2:$C$5,A2) in the formula bar and press F9, you'll see an array of numbers and #VALUE! (don't forget to press Escape, otherwise you'll replace the formula with its result!)

I then added ISNUMBER:

=ISNUMBER(FIND($C$2:$C$5,A2))

This returns an array of TRUE/FALSE values. I wanted to know the position of the first TRUE (if any):

=MATCH(TRUE,ISNUMBER(FIND($C$2:$C$5,A2)),0)

This must be an array formula. It returns either a number between 1 and 4 or #N/A. If it is a number, it is the index into C2:C5 where the match is found. So I use the INDEX function to return the matching word:

=INDEX($C$2:$C$5,MATCH(TRUE,ISNUMBER(FIND($C$2:$C$5,A2)),0))

(Again, this is an array formula)
Finally, I wrapped it all up in IFERROR to suppress #N/A for non-matches.

[Rudi]

TX. I build up formulas in the same way, but I see my error occurred right in the beginning...
When I experimented, I too was using Find, but I started with =FIND($C$2:$C$5,$A$2:$A$5) and it never worked... I understand the array formula concept and have no issues with the common array formulas like: =SUM((A1:A10>=100)*(B1:B10="Test")*(C1:C10)), etc.., but there heavy ones I still need to play with.
Cheers!