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 1^{st} 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 🙂