Hi all,
I'm trying to use the formula found in the following link: https://www.myonlinetraininghub.com/excel-search-string-for-a-list-of-words. However, I cannot work out how to add a larger list. What I have tried to do is extend the list variable to being 4 cells rather than 3 and then changed the ROW($1:$3) term to be ROW($1:$4). That does not work.
Any suggestions?
Thanks.
Hi,
Use the "SEARCH Not Case Sensitive Array Formula" and change the ROW reference to match the number of items in your 'List'. e.g. if you add one more item to the 'List' your formula would be:
<pre>=INDEX(list,LARGE(IF(ISNUMBER(SEARCH(list,A2)),ROW($1:$4)),1))</pre>
Entered with CTRL+SHIFT+ENTER as it's an array formula.
Mynda
You could also use LOOKUP like this:
=IFERROR(LOOKUP(1E+100,1/SEARCH(list,A2),list),"No match")
Hi,
It is a nice trick Velouria, I had to play around a little to understand what it does. This is why I like this forum, you get to learn new ways to work with Excel.
I noticed though that as you divide 1 by the result of the SEARCH function, the value will never exceed 1, so you should be able to lookup 1 instead of 1E+100 (or 1E+306 as I have seen in many other examples).
Have I got it correct?
/Anders
Hi Anders,
You are correct. It’s force of habit I think that made me write 1E100! In truth, you could either use:
=IFERROR(LOOKUP(1E+100,SEARCH(list,A2),list),"No match")
or:
=IFERROR(LOOKUP(1,1/SEARCH(list,A2),list),"No match")
