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

____________________________

35 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

    :)

  • Ganesh

    I have used this formula to search multiple texts in a cell.
    Search list contains several texts having few common texts within them e.g. "PB 15" and "PB 15:2".
    This formula returns "PB 15" while searching for "PB 15:2" also.
    could you kindly inform if it could be restricted to exact match, in this case "PB 15:2".
    thanks for the help..

    • Andy

      According to your question, a simple solution is to place PB 15:2 above PB 15 on the list. This answers your post, but you have to go through the trouble of listing the names above or below something else.

  • Ganesh

    Thank you, Andy.
    But the list I am searching is too long (50K rows). Hence need some auto solution please..

    • Andy

      Ganesh,

      You can create a table to your list of 50k rows. After you have done so, you can arrange the names via sorting z to a using the table options. This should give PB15:2 above PB15.

  • Jemma

    Hello, hope you don't mind me asking but I was wondering you could help. I'm trying to set up a spreadsheet which is linked to a diary system (all excel) what I'm trying to do with the linked spreadsheet is for it to show "booked" when any text is shown in a particular cell from the diary. I want to put the codes in for each day, so 1st July = if something (any text) is in the diary it says "booked" if no text it says "available".

    Have you got an idea of how to do this??

    • Andy

      Jemma,

      You may use a combination of If and Counta functions to respond if the chosen spreadsheet contains any non-blank cells.

      Let's assume we have two different spreadsheets "A" and "B"

      When this code is used in Spreadsheet "A" in cell A1, you can find out whether anything is written in the spreadsheet "B"

      =If(Counta([B.xlsx]Sheet1$1:$1038576)>0,"Booked","Available")

      *Counta formula responds with a number count of any non-blank cells.
      *When counta formula responds with a # greater than 0, that means that there is something written in the spreadsheet vs. 0, which would mean that there isn't anything written in the spreadsheet.
      *The range 1:1038576 is the selection of the entire spreadsheet in Excel 2007.
      *If function responds with a specified condition to be true or false.

  • Jorge Cue

    Hello,

    Is there a Macro to get the text without have to enter the formula in the column B?

    Thanks,
    Jorge

  • Jorge Cue

    Is there a way to get the text instead of the formula in column B in the first example.

    Thank you!!!

    Jorge

  • Jorge Cue

    Hello,

    I just want to make a comment:

    I have seen many ways to solve a problem but THERE IS NOTHING explained as this one.

    THUIS IS THE BEST I'VE EVER SEEN IN 20 YEARRS!!!

    Thank you so much!!!

    Jorge Cue

  • Monique

    Hi Debra,

    I have tried using the get code with index but excel seems to struggle over 20k rows where string within each cell can be quite long. Can you suggest any alternatives how this formula can work quickly? Many thanks

  • Mike

    Hi Debra.
    Am I right in thinking that the formula =COUNTIF(A1,"*" & $D$1:$D$3 & "*") counts the number of cells that contain at least one instance of the codes? Not the number of times the codes appear in each cell?

    Any suggestions on how to count the number of times the codes in Column D appear in Column A when the codes can appear multiple times in the same cell?
    A1: CAT ABC CAT
    A2: XYZ CAT

    Expected result: 3

    Thanks.

  • Bushra

    Can anyone here Help me on below sample data
    I have more then 1000 entries of repeating names in "data file" these names are to be replaced with codes provided in separate sheet, i have tried lookup function but its not working with this data.

    File 1 : "Data File" has this format

    Names:

    Madiha Siraj
    Nayha
    Arneeb Farooq
    Noreen Huma
    Madiha Siraj
    Shagufta Parveen
    Nayha

    File 2: "Codes File"

    Names Codes

    Dr.Nayha Kahlid 1085
    Dr.Madiha Siraj 1081
    Dr.Shagufta Parveen 1010
    Dr.Arneeb Farooq 1009
    Dr.Noreen Huma 1002

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>