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
Enter your email address below to download the sample workbook.
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.
Antonio says
Hello,
I’m trying to use this formula to extract cities names within a string using a separate list of cities but I found it is not sensitive enough to get the exact city name. For example, I have the following address in E2:
Department of Life Sciences, MRC Centre for Molecular Bacteriology and Infection, Imperial College London, London SW7 2AZ, United Kingdom
Let’s say I have a list of all the cities in the uk (list name: “UK”) and I’m comparing that list against the list of addresses I have, one by one. How could I extract the correct city name?
The format of the addresses I have are not standardized, so the cities may end up in different positions with the string and also may or may not have delimiters between the city names.
Mynda Treacy says
Hi Antonio,
Please post your question and sample Excel file with the scenarios you’re referring to on our Excel Forum so we can see your question in context and help you further.
Mynda
Johannes says
Kinda lost here. I have a situation where I expect multiple matches (up to 20) and I want to output all of them. Is there anyway how I can return each value one by one?
Mynda Treacy says
Hi Johannes,
It’s difficult to say without seeing your scenario in an Excel file. Can you please post your question and sample file on our Excel Forum where we can give you a specific solution.
Thanks,
Mynda
Scott M says
hi Mynda
This scenario solution as exactly what I was looking for except it only works in your test situation and not in my spreadsheet. I downloaded your sample file and modified it and that also started giving incorrect results. This is what I did with your file.
* I added more terms to your list (col H) so it went down another 8 rows.
* I edited the name list so it went from row 2 to row 12
* I edited your formulas so the “ROW($1:$3)” became “ROW($1:$11)”
Hit F9 for refresh
in the first 2 formula columns I got #N/A now
in the last 2 formula columns I got a results but they chose options from my list that were incorrect.
If i just left the list from rows 1:3 and changed names in the list or modified the string in column A, the formulas worked fine, but when I added more items to the list and changed the named named range it would go screwy. such a shame as it exactly what I am looking for except in my situation the lookup named range is 120 rows.
Mynda Treacy says
Hi Scott,
Happy to troubleshoot further for you if you can post your question and Excel file in our forum here: https://www.myonlinetraininghub.com/excel-forum
Cheers,
Mynda
david says
Hi I’m looking for a vba solution for the following:
Sheet1 Col A: Group Name
Col B: list of comma separated terms (for this Group)
Sheet2 Col A: text string
Col B: Group Name
I need to loop thru all the Groups in Sheet 1 and check if any of the terms in Sheet1 ColB appear in the text in Sheet2 Col A, if a match is found then copy the Group Name (Sheet1 ColA) to Sheet2 ColB and continue with the next cell in Sheet2 Col A.
What I have so far is:
For each cell in Sheet2 Col A
For each Group in Sheet1 Col A
If a word in Sheet2ColA is found in Sheet1 ColB then
Copy Sheet1ColA (Group Name) to Sheet2 ColB
Exit For
End If
Next Group
Next cell in Sheet2 ColA
Need help to write the VBA
Thanks
David
Catalin Bombea says
Hi David,
Please prepare and upload a sample file on our forum, it will be easier to work with a sample of real data.
Catalin
david says
Hi Catalin, I found a solution, I’ll try and load it to the forum.
Thanks
David
Catalin Bombea says
Hi David,
Thanks for feedback. If you need more help, we’ll continue on forum.
Cheers,
Catalin
Dave Norton says
This is a fantastic solution, it works perfectly for me when expanding the index array to two columns and returning the second column as a “lookup” value from the list. Thank you!
Catalin Bombea says
Glad to see you’re happy Dave, thanks for the kind words 🙂
Dave Norton says
Thank you for your help 🙂 One build I made… I had a scenario where two matches were made from my list – for example, row 1 and row 4 of my list of search criteria were matched, and therefore the SUMPRODUCT returned ‘5’, so I replaced the SUMPRODUCT with a MAX to return the forth item as this suited what I needed.
Catalin Bombea says
Thanks for feedback Dave, indeed there are cases when multiple matches can be found.
Jappie says
My list doesn’t start in row one, what would be the alternate of my list doesn’t start in row one?
Thank you
Mynda Treacy says
Hi Jappie,
You just need to make sure the named range ‘list’ points to the cells containing your list.
And if your list contains more than 3 rows then you change the ROW formula to suit. e.g. if your list is 5 rows high then the ROW part of the formula will be ROW(1:5)
If you get stuck please post your question on our Excel forum with your sample Excel file so we can help further.
Mynda
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 🙂