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

## 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.

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: http://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 🙂