Excel Formula to search multiple text from a list in a cell and return the searched text

Two key questions:

1. how many search terms in your real-life dataset (D2:D8 in above) ?

2a. are you, definitely, running XL2013? we ask because if using either 2016 or O365 things become a lot simpler, in terms of native functions
2b. do you have backwards compatibility issues -- I ask because you're using ISERROR (and double evaluation) rather than IFERROR - latter not available until XL2007.

Originally Posted by XLent

Two key questions:

1. how many search terms in your real-life dataset (D2:D8 in above) ?

2a. are you, definitely, running XL2013? we ask because if using either 2016 or O365 things become a lot simpler, in terms of native functions
2b. do you have backwards compatibility issues -- I ask because you're using ISERROR (and double evaluation) rather than IFERROR - latter not available until XL2007.

1. Around 50 to 60 terms in column D
2a. Office 2010 in office and office 2013 at home solution required for office
2b. No backward compatibility issue

OK; so one formula based option - based on above - would be as follows:

Formula:

Please Login or Register  to view this content.

You could achieve the same with a succinct UDF but it all comes down to whether or not using code is problematic for you.

Great.Its working as expected. thanks.

Also, do you have example of succinct UDF

I'm not on my usual machine so don't have any existing code to hand but something like the below should work, and offer flexibility -- obviously it would account for 1 to n matches (unlike the formula)...

Place in standard module,and then called via worksheet as:

=CONCAT_CRIT($A2,$D$2:$D$8)

Can also be used to find all search terms across all cells, in single cell, e.g.

=CONCAT_CRIT($A$2:$A$9,$B$2:$B$8)

And has optional variables should you want to modify delimiter, alternative return (for 0 finds) and/or handle duplicity of terms if doing multiple searches where duplicate terms a possibility, e.g.

=CONCAT_CRIT($A$2:$A$9,$B$2:$B$8,"^&",,FALSE)

would give a very different answer to previous despite ranges being same...

default behaviour: Options,Equity,Fund,Revenue,Legal,Compliance
modified behaviour: Options^&Equity^&Fund^&Revenue^&Legal^&Legal^&Compliance^&Equity^&Fund

Please Login or Register  to view this content.