Find Text With INDEX and MATCH

Is there a harder working team in Excel, than the reliable duo of INDEX and MATCH? These functions work beautifully together, with MATCH identifying the location of an item, and INDEX pulling the results out from the murky depths of data.

Last week, Jodie asked if I could help with a problem, and INDEX and MATCH came to the rescue again.

Find the Text in a String

Jodie sent me a picture of her worksheet, with text strings in column A and codes in column D. Each text string contained one of the codes, and Jodie wanted that code to appear in column B.

Would you use INDEX and MATCH to find the code, or another method? Keep reading to see my solution, and please share your ideas, if you have other ways to solve this.

indexmatchtextstring01

Count the Occurrences With COUNTIF

When you want to find text that's buried somewhere in a string, the * wildcard character is useful. We can use the wildcard with COUNTIF, to see if the string is found somewhere in the text.

I entered this test formula in cell B1. This formula needs to be array-entered, so press Ctrl + Shift + Enter.

=COUNTIF(A1,"*" & $D$1:$D$3 & "*")

indexmatchtextstring02

There are wildcard characters before and after the cell references to D1:D3, so the text will be found anywhere within the text string.

To see the results of the array formula, click in the formula bar, and press the F9 key. The array shows 0;1;0 so it found a match for CAT, which is in the second cell in the range.

indexmatchtextstring03

Get the Position With MATCH

Press the Esc key, to exit the formula without saving the calculated results.

Next, you can add the MATCH function, to get the position of the "1" in the results. Remember to press Ctrl + Shift + Enter.

=MATCH(1,COUNTIF(A1,"*"&$D$1:$D$3&"*"),0)

indexmatchtextstring04

The result is 2, so the code "CAT" was found in cell A1.

Get the Code With INDEX

Next, the INDEX function can return the code that is at the position that the MATCH function identified. Press Ctrl + Shift + Enter to see the result.

=INDEX($D$1:$D$3,MATCH(1,COUNTIF(A1,"*"&$D$1:$D$3&"*"),0))

indexmatchtextstring05

The result is CAT, so the formula is working correctly.

Prevent Error Results With IFERROR

There should be a valid code in each text string, but sometimes the data doesn't cooperate. Just in case there are text strings without a code, or more than one instance of the code, you can use IFERROR to show an empty string, instead of an error. (Excel 2007 and later versions)

=IFERROR(INDEX($D$1:$D$3,MATCH(1,COUNTIF(A1,"*"&$D$1:$D$3&"*"),0)),"")

Enter with Ctrl + Shift + Enter, and then copy the formula down to row 10.

indexmatchtextstring06

In cell B6, the formula returns an empty string, and the cell looks blank, because none of the valid codes are in the text that's in cell A6.

Use a Named Range

Instead of referring to range $D$1:$D$3, you could name that range, and use the name in the INDEX/MATCH formula. That would make it easier to maintain, if the size of the codes list will change.

indexmatchtextstring07 

More INDEX and MATCH Examples

There are more examples of using INDEX and MATCH in the 30 Excel Functions in 30 Days series:

30 Excel Functions in 30 Days – INDEX

30 Excel Functions in 30 Days – MATCH

____________________________

23 comments to Find Text With INDEX and MATCH

  • Detlef

    Hello Debra

    Without CSE and shorter:
    =IFERROR(LOOKUP(2,1/SEARCH($D$1:$D$3,A1),$D$1:$D$3),"")

  • Greetings, Debra.
    I enjoyed your post describing how to use countif, index, and match to parse a complex string. I tried it myself with my own dataset and found that I was unable to use a range name in place of the absolute reference to the list range. Seems like it should work since a named range is treated as an absolute reference, or so I thought. Any ideas what might be going on? Yes, I've checked the spelling, etc. Can send my sample file if it would be of interest.
    Thanks!
    Paula

  • @Debra and other readers of this thread,

    This blog article inspired me to revisit a function I had posted to my own mini-blog site, the result being a new function that implemented the list searching functionality presented in your article, but extended to handle single or multiple words searches where the sought after word stood alone, as a word, not embedded along with other text... and further extended to allow the user to customize the characters that would be considered as "word break" characters. For those who might be interested, here is a link to my mini-blog article...

    http://www.excelfox.com/forum/f22/findword-find-possibly-listed-word-word-not-embedded-within-another-word-603/

  • Jorge

    How can I count the number of times a specific word or text string appears in the COMMENTS of a spreadsheet?

    Jorge

  • Andy

    Jorge - you'd need to do that in VBA. Here's one (undocumented and only slightly tested) function that'd do it:
    Public Function CountWordInSheetsComments(strWordToFind As String) As Long

    Dim C As Comment, mySheet As Worksheet, lCount As Long

    Set mySheet = Application.Caller.Parent
    lCount = 0

    For Each C In mySheet.Comments
    If InStr(1, C.Text, strWordToFind) > 0 Then
    lCount = lCount + 1
    End If
    Next C

    CountWordInSheetsComments = lCount

    End Function

  • Claudio

    Thank you, very good formula. I suggest attaching a file as a model for your application, for those who follow your blog from Latin America.

    Claudio

    Gracias, muy buena formula. Sugiero adjuntar un archivo como modelo de su aplicacion, para los que seguimos su blog desde latinoamerica.

    Claudio

  • BP

    I know this is a couple of months old, but it's the newest post I can find that is similar to what I need.

    I have a list of reps for work that are in a column and a list of their roles (abbreviated) in the adjacent column, similar to this:

    Rep_1 A
    Rep_2 I
    Rep_3 R
    Rep_4 T
    Rep_5 I

    There is a full list and there are multiple reps in the same role. The list comes in order based on reps name. I want to separate the reps out by role on a separate sheet, to look something like this:

    Approver (A):
    Rep_1
    .
    .

    Investments (I):
    Rep_2
    Rep_5
    .
    .

    and so on. I was hoping an Index & Match function would work with this, but not able to figure it out. Any help with this would be greatly appreciated.

    Thanks,
    BP

  • Hugo

    This can be done easily by creating a pivot table based on a data source including your data.
    Then put the roles on the most left side of row headings and the reps next.

  • suresh

    Very very thanks for Find Text With INDEX and MATCH solution . So nice you.

  • Morton Wakeland

    Debra - Mort W here. Am still trying to learn and grasp how to use the combo of MATCH & INDEX.
    Look at the text just above the 3 picture in your post, where you have in the formula bar:
    ={0;1;0} - this is in cell B1. You state,"array shows 0;1;0 so if found a match for CAT, which
    is second cell in the range. I thought we were seeing if CAT appeared in cell A1, you say,
    "which is in the second cell in the range." The second cell in the range appears to refer
    to the codes: DOG CAT PIG, and not to the string in A1 - ABC CAT TYPYUTYU. Am I misunderstanding
    something here...THANK YOU FOR CLARIFYING! Mort

    • Mort, the range that I'm talking about is the list in D1:D3 -- Dog, Cat, Pig.
      Here is the formula that is in cell B1 in that screen shot:
      =COUNTIF(A1,"*" & $D$1:$D$3 & "*")

      The formula is checking the contents of cell A1, to see if it can find Dog, Cat or Pig, anywhere in cell A1's text.

      When you look at the results after pressing the F9 key, it shows {0;1;0}
      That is the result of testing cells D1, D2 and D3, to see if they are found in cell A1.

      --D1 and D3 (dog and pig) were not found in cell A1's text, so they are 0
      --D2 (cat) was found, so it has a count of 1.

  • Cyril

    Hi,

    May I ask what would you recommend for returning all text within a complex text string regardless of their position, such as in:
    "the brown cat ate the food of the grey dog" would return {0,0,1,0,0,0,0,0,0,1}
    A count would return 2 as two words (dog and cat) are found within the CodeList.
    I am presently using =SUMPRODUCT((LEN(A2)-LEN(SUBSTITUTE(A2,list,"")))/LEN(Codelist)) as it would return the 'COUNT" of the text found within the CodeList regardless of their position in the cell A2.

    Thanks for your comment.

  • Clare

    Hi Debra,

    Thanks for another wonderful Post on using Match with Index. My question on this subject will require providing an Excel file with some sample data. I hope it's OK if I email that to you for your help.

    Thanks heaps for everything.

    Clare

  • mucha

    Hello Debra,

    I do appreciate your help. Would it be possible to add an exact match to your formula? At the moment if the word searched is embedded in another word a "false positive" is generated. Would it be simple to add this to your formula?

    Thanks again for your help.

  • Scott

    This is really a great example. Thank you.

    I was wondering, is it possible to return the cell references, or cell values if the text that is searched for appears in more than one cell in a range.

    To make is simpler, the search text could even be just one cell. If I want to find "bloggers" in A1:A20, and it appears in A3 and A5, can I get a result that either shows 1,5 or maybe copy the contents of those cells into new cells?

    I can find tons of help on finding values in arrays - but all of them either return only the first occurrence, or list how many occurrences. None help me locate (index) more than one occurrence or show their values.

  • Dash

    many thanks for the advice. useful!

  • This would also help
    =LOOKUP(1E+100,SEARCH(D$1:D$3,A1),D$1:D$3)

  • Patrick

    Hi,

    thanks a lot for this post, it was a great help.
    I was able to replicate this for my purposes, but was wondering if we can take it one step further - this is my data:

    These examples of the original names:

    NL > NL > VO > SM > Hoofddorp
    NL > NL > OF > SM > Hoofddorp
    NL > NL > Brand > SM > Hoofddorp
    NL > NL > VO > SM > Amsterdam
    NL > NL > VO > SM > Utrecht

    These are examples of the elements I need to find in the original names:
    > VO
    > OF
    > MR
    > Brand
    - Büro
    - VO
    - Coworking

    Then, I want to assign each of these elements to another definition, but some of them will have the same definition, e.g.:
    > OF
    - Coworking
    - Büro

    They all would have to be defined as "Office" - I can do this obviously with a seperate VLOOKUP formula, but do you think it's possible to do this all in one formula?

    Thanks a lot,
    Patrick

  • No worries - was just able to do it myself :-)

    Here is my solution:

    =VLOOKUP((INDEX(ProductList,MATCH(1,COUNTIF(D2,"*"&ProductList&"*"),0))),MatchProduct,2,FALSE)

    ProductList & MatchProduct are both named ranges.

    Any more efficient solution maybe?

    Thanks anyways,
    Patrick

  • Nick

    Hello Debra,

    I have been using the code for the VLookupInvMark.xls, thank you.

    I now need the same output but when I Type an X in column J (so data is to the left).

    Can you help?

  • Andy

    Hello Debra,

    This is a great post! I was fortunate enough to come across this reading to help me construct my own Excel template. I have followed your code {=IFERROR(INDEX(CodeList,MATCH(1,COUNTIF(A1,"*"&CodeList&"*"),0)),"")}, and the formula does exactly as you have explained in your article. However, there is one problem I came across with the formula in the spreadsheet that I'm working on. When there is a blank field located in the middle of the list, the function does not account for the texts listed below. For example, when the text "CAT" is taken out from your codelist, the text "PIG" is no longer displayed in the column B. This is where I'm stuck. If you know how to solve this problem and provide me with the formula, I'd highly appreciate it.

    Thank you

    :)

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>