A while back Vernon asked me how he could search one cell and compare it to a list of words. If any of the words in the list existed then return the matching word.
Ugh, I’ve written that 3 times and I’m not sure it’s any clearer…let’s look at an example.
I’ve highlighted the matching words in column A red.
What we want Excel to do is to check the text string in column A to see if any of the words in our list in H1:H3 are present, if they are then return the matching word. Note: I’ve given cells H1:H3 the named range ‘list’.
There are a few ways we can tackle this so let’s take a look at our options.
Warning: this is quite an advanced topic which requires an array formula to solve it.
Non-Case Sensitive Matching
If you aren’t worried about case sensitive matches then you can use the SEARCH function with INDEX, SUMPRODUCT and ISNUMBER like this:
=INDEX(list,SUMPRODUCT(ISNUMBER(SEARCH(list,A2))*ROW($1:$3)))
In English our formula reads:
SEARCH cell A2 to see if it contains any words listed in cells H1:H3 (i.e. the named range ‘list’) and return the number of the character in cell A2 where the word starts. Our formula becomes:
=INDEX(list,SUMPRODUCT(ISNUMBER({20;#VALUE!;#VALUE!})*ROW($1:$3)))
i.e. in cell A2 the ‘F’ in the word ‘Finance’ starts in position 20.
Now using ISNUMBER test to see if the SEARCH formula returns any numbers (if it does it means there is a match). ISNUMBER will return TRUE if there is a number and FALSE if not (this gives us a list of Boolean TRUE or FALSE values). Our formula becomes:
=INDEX(list,SUMPRODUCT({TRUE;FALSE;FALSE}*ROW($1:$3)))
Use the ROW function to return an array of numbers {1;2;3} (see notes below on why I've used ROW in this formula). Our formula becomes:
=INDEX(list,SUMPRODUCT({TRUE;FALSE;FALSE}*{1;2;3}))
When you multiply Boolean TRUE/FALSE values they become their numeric equivalents i.e. TRUE = 1 and FALSE = 0. So our formula evaluates this ({TRUE;FALSE;FALSE}*{1;2;3}) like so: {1*1, 0*2, 0*3} and our formula becomes:
=INDEX(list,SUMPRODUCT({1;0;0}))
SUMPRODUCT simply sums the values {1+0+0} which gives us 1. Note: by using SUMPRODUCT we are avoiding the need for an array formula that requires CTRL+SHIFT+ENTER. Our formula becomes:
=INDEX(list,1)
Index can now go ahead and return the 1st value in the range of cells H1:H3 which is ‘Finance’.
Note: the above formula will not work if a match isn't found. If you want to return an error if a match isn't found then you can use this variation:
=INDEX(list,IF(SUMPRODUCT(ISNUMBER(SEARCH(list,A2))*ROW($1:$3))<>0,SUMPRODUCT(ISNUMBER(SEARCH(list,A2))*ROW($1:$3)),NA()))
Not as elegant, is it? In which case you might prefer one of the array formulas below.
Notes about the ROW Function:
The ROW function simply returns the row number of a reference. e.g. ROW(A2) would return 2. When used in an array formula it will return an array of numbers. e.g. ROW(A2:A4) will return {2;3;4}. We can also give just the row reference(s) to the ROW function like so ROW(2:4).
In this formula we have used ROW to simply return an array of values {1;2;3} that represent the items in our 'list' i.e. Finance is 1, Construction is 2 and Safety is 3. Alternatively we could have typed {1;2;3} direct in our formula, or even referenced the named range like this ROW(list).
So you see using the ROW function is just a quick and clever way to generate an array of numbers.
What you must bear in mind when using the ROW function for this purpose is that we need a list of numbers from 1 to 3 because there are 3 words in our list and we’re trying to find the position of the matching word.
In this example the formula; ROW(list) will also work because our list happens to start on row 1 but if we were to start 'list' on row 2 we would come unstuck because ROW(list) would return {2;3;4} i.e. 'list' would actually reference cells H2:H4.
So, don’t get confused into thinking the ROW part of the formula is simply referencing the list or range of cells where the list is, the important point is that the ROW formula returns an array of numbers and we're using those numbers to represent the number of rows in the 'list' which must always start with 1.
Functions Used
ROW - Explained above.
Case Sensitive Matching
[updated Dec 5, 2013]
If your search is case sensitive then you not only need to replace SEARCH with FIND, but you also need to introduce an IF formula like so:
=INDEX(list,IF(SUMPRODUCT(ISNUMBER(FIND(list,A2))*ROW($1:$3))<>0,SUMPRODUCT(ISNUMBER(FIND(list,A2))*ROW($1:$3)),NA()))
Unfortunately it's not as simple or elegant as the first non-case sensitive search. Instead the array formulas below are nicer
Array Formula Options
Below are some array formula options to achieve the same result. They use LARGE instead of SUMPRODUCT, and as a result you need to enter these by pressing CTRL+SHIFT+ENTER.
Non-case Sensitive:
[updated Dec 5, 2013]
=INDEX(list,LARGE(IF(ISNUMBER(SEARCH(list,A2)),ROW($1:$3)),1)) Press CTRL+SHIFT+ENTER
Case sensitive:
[updated Dec 5, 2013]
=INDEX(list,LARGE(IF(ISNUMBER(FIND(list,A2)),ROW($1:$3)),1)) Press CTRL+SHIFT+ENTER
Download
Thanks
Special thanks to Roberto for suggesting the 'updated' formulas above.
Please Share
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+1, Facebook and Twitter.
Kirsty Bell says
I thought this was extremely helpful, until I hit a problem.
I have a list of cities, however some cities appear twice, with the second city an abbreviated version of the first.
For instance in cells H1 and H2…
CHICAGO
CHIC
The formula will work for CHIC, but not CHICAGO.
In A2 and A3…
CHICAGOMPREMIX-11310717-b
CHICFIELD—61290717
Is there any way around this???
Catalin Bombea says
Hi Kirsty,
You forgot to mention which formula are you using.
The SUMPRODUCT version has a downside: if more than a keyword is found, SUMPRODUCT will return the sum of their row positions, which will be wrong. Use the formulas from the end of the article:
=INDEX(list,SMALL(IF(ISNUMBER(SEARCH(list,A1)),ROW($1:$2)),1))
This will return only the first keyword found.
Catalin
Carlos Avila says
HI Mynda, Please help me with this:
I need search-find numbers in a cell values is separated by comma, like this 8068,8069,8208,8713 | 7851,7859 | 7851,8208,8252 | 7738 | on this file ISNUMBER() is FALSE, “numbers” is on ROW ($1:$4) (file with 1500 rows), I not want split column, just want back TRUE or FALSE, in case of number is in cell separated by comma, or back number of ROW.
Thanks for ALL your Blogs and Tricks send by email.
Mynda Treacy says
Hi Carlos,
Can you please post your question and a sample file on our Excel Forum. I’m having trouble visualising your data structure.
Thanks,
Mynda
PhilC says
Thanks for a well worked / explained approach, was able to use with my data extremely easily. Appreciate it.
Cheers Phil
Philip Treacy says
You’re welcome.
Shane Johnson says
I changed the very first formula to: =INDEX(list,SUMPRODUCT(ISNUMBER(SEARCH(list,H2))*ROW(list))) since my data was in H2 versus A2. When I enter this formula and drag it down, it simply pulls the key words in order instead of returning the keyword if found in the H row cells.
Document Header Text Found Keyword
RRC PMO Accr AAA
Move Indy Dec Acquisition
Jan 2016 RRMC Rent Admirals Club
I wish I could paste a picture to show you but as you can see from my example there is no “AAA” in “RRC PMO Accr” and “AAA” is the first keyword on the list, “Acquisition” is the second, etc. So instead of searching the list it is simply pulling the next keyword and placing it in the cell.
Mynda Treacy says
Hi Shane,
Please post your question and sample file on our Excel Forum, that way we can see what you’re working with and help you.
Cheers,
Mynda
hutuka says
Thank you so much, this was really helpful!
Philip Treacy says
You’re welcome
S. Narasimhan says
Dear sir,
If the cell A2 contains two words from the list, can they be extracted one by one?
What happens if there are no words from the list? What message will be displayed?
If there are more words from the list contained in cell A2 can they be counted?
Thanks and regard
André says
Thank you for excellent on line tutoring.
Mynda Treacy says
You’re welcome, André 🙂
Haseeb A says
Another non Array:
=LOOKUP(9.9E+300,SEARCH(List,A2),List)
If List may contains blanks,
=LOOKUP(9.9E+300,SEARCH(List,A2)/(List””),List)
Will give last matchable item from the List.
Mynda Treacy says
Your first formula is brilliant, Haseeb 🙂
And of course replace SEARCH with FIND for case sensitive searches.
Your second formula is giving an error though.
Thanks for sharing.
Mynda.
Haseeb A says
Hello Mynda,
My original formula was (List LessThan GreaterThan “”). (Of course without space)
But, I think due to HTML code converted to “”. It keeps ignoring Lesstah GreaterThan symbols.
Or with LEN(List)
Haseeb
Mynda Treacy says
Ah, that would explain it. You need to use HTML tags when using > or < symbols.
r says
Hi,
this formula (no array formula) solves the problem of multiple matches
=INDEX(list,MATCH(1=1,INDEX(ISNUMBER(FIND(list,A2)),0),))
regards
r
Mynda Treacy says
Fantastic! Thanks for sharing, r 🙂
r says
glad you like it 🙂
Matheus says
Mynda,
Great article!
Could you explain this formula? I would like to know how R’s formula works.
Thanks,
Matheus
Mynda Treacy says
Hi Matheus,
Thanks!
It’s probably easiest to look at how each function evaluates in order. Here is the formula:
=INDEX(list,MATCH(1=1,INDEX(ISNUMBER(FIND(list,A2)),0),))
FIND says find the position of any of the words in the ‘list’ in cell A2:
=INDEX(list,MATCH(1=1,INDEX(ISNUMBER({20;#VALUE!;#VALUE!}),0),))
If FIND returns a number then ISNUMBER returns a ‘TRUE’:
=INDEX(list,MATCH(1=1,INDEX({TRUE;FALSE;FALSE},0),))
INDEX passes an array of TRUE and FALSE values to MATCH:
=INDEX(list,MATCH(1=1,{TRUE;FALSE;FALSE},))
The MATCH function says Lookup TRUE (1=1) in the array returned by INDEX. The position of TRUE is 1.
MATCH: =INDEX(list,1)
Result: Finance
You might find this tutorial helpful in understanding complex formulas: https://www.myonlinetraininghub.com/excel-formulas-not-working
Kind regards,
Mynda
Matheus says
Thanks Mynda!
You’re awesome!
Isaac Broberg (Gloria Garcia says
I’m trying to figure out how all these formulas work – when I put in =ISNUMBER(SEARCH(list,A2)) the result ends up being FALSE. Why doesn’t it show me the three results as you have above (ISNUMBER({20;#VALUE!;#VALUE!})) which also appears when I evaluate the formula?
Catalin Bombea says
Hi,
Those results are the results of excel’s background calculations. Excel will never show you how it does calculations, it will simply give you the result. (ISNUMBER({20;#VALUE!;#VALUE!})) is just a detailed description used to describe what happens inside those functions.
Hope it’s clear enough,
Cheers,
Catalin
pmsocho says
Cool formulas. Thanks for sharing!
Mynda Treacy says
Cheers, pmsocho 🙂
Michael Rempel says
I like your explanations and the way you go through each step. It makes what could be very difficult seem simple. thanks!
Mynda Treacy says
Thank you, Michael 🙂
Bryan says
Very clever. I probably would have had to resort to a UDF.
The only watchout is if you get more than one match, you will get an unexpected result. For instance, if the cell tested contains “Finance” AND “Construction”, the formula will result in “Safety”, since 1 (Finance) + 2 (Construction) = 3 (Safety).
Mynda Treacy says
Cheers, Bryan. Good point about multiple matches.
Khurram says
wow great idea, yet not implemented but seems will work great to sort out my long list to categorized.
Thanks in advance.
Philip Treacy says
Cheers, Khurram 🙂