Log In
Register
Home
New Member
November 23, 2018
Offline
Hi all,
I'm trying to use the formula found in the following link: https://www.myonlinetraininghu.....t-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.
July 16, 2010
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
You could also use LOOKUP like this:
=IFERROR(LOOKUP(1E+100,1/SEARCH(list,A2),list),"No match")
VIP
Trusted Members
December 7, 2016
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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")
1 Guest(s)
