Find Text With INDEX and MATCH

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. See how to find text with INDEX and MATCH.

Find Text in a String

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

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, $D$1:$D$3.

  • Important: After you check the results, press the Esc key, to exit the formula without saving the calculated results.

indexmatchtextstring03

Get the Position With MATCH

Next, you can add the MATCH function, wrapped around the COUNTIF formula, to get the position of the “1” in the results.

Make the following change to the formula in cell B1, and remember to press Ctrl + Shift + Enter.

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

indexmatchtextstring04

The result is 2, so the code “CAT”, the 2nd item in range D1:D3, was found in cell A1.

Get the Code With INDEX

Next, the INDEX function can return the code from the range $D$1:$D$3, that is at the position that the MATCH function identified.

Make the following change to the formula in cell B1, and remember to press Ctrl + Shift + Enter.

=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 one 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

Download Find Text With INDEX and MATCH Sample File

To get the sample file, and see how the formula works, go to the INDEX and MATCH page on my Contextures site.

In the Download section on that page, look for sample file 4 – Find Text From Code List. The zipped file is in xlsx format, and does not contain macros.

More INDEX and MATCH Examples

There are more examples of using INDEX and MATCH on my Contextures site.

For example, this video shows how to use INDEX and MATCH to find the best price.

____________________________

Find Text With INDEX and MATCH

58 thoughts on “Find Text With INDEX and MATCH”

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

  2. @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/

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

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

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

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

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

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

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

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

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

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

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

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

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

  15. 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?

  16. 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
    🙂

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

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

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

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

  19. 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??

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

  20. Hello,
    Is there a Macro to get the text without have to enter the formula in the column B?
    Thanks,
    Jorge

  21. Is there a way to get the text instead of the formula in column B in the first example.
    Thank you!!!
    Jorge

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

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

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

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

  26. Hi Debra, can you provide an spreadsheet where I can see the formulas working? Step 2 “Count the Occurrences With COUNTIF” does not work at all for me. I do always get the excel error #NV, which seems to be an inconsistent cell format

  27. Hi Debra, I have a question that I was hoping you could help me with. I’m trying to calculate a frequency and to do this I have a Yes/No column if a filter was changed and a corresponding time column for each time it was checked to see if it needed changing. If it says Yes then it would take the time of the coressponding time column and subtract the time of the last time that the filter was changed (last time the column said Yes). It’s proabaly really easy but I’m having trouble with it. Thanks in advance!
    Thanks,
    Ben

  28. Hi Debra,
    Thank you for this very helpful function. If you needed to add codes to column E, so that you needed to ‘lookup’ your codes in an array from D$1:E$3, how would you do this within your formula? I tried to change your formula in both places to D$1:E$3 but it didn’t find any codes anymore?
    Thanks,
    Aaron

  29. i have 2 excel work sheet i need to take data from one to another worksheet via matching character is it possible in excel worksheet
    please provide me code or function or any information

  30. Wondering if someone can help me….
    I have a list of data containing different info, download from bank statement.
    Example: In B1
    DBDP25207480013 MSP
    CHQ#00128-0500099095
    I would like to look up this info , B1, compare it to a table with information:
    e f
    MSP First Data
    ATM Cash
    and if MSP in the B1 cell, the value in c2 would return First Data, when looking at the table information e1-MSP and return F1 First Data
    Can any one help with this,
    I have tried different things and cannot get it to work

    1. Christine,
      I have been working on the exact same project and would be interested to discuss further what you have found.If you are still working with this project or if you have solved it and are willing are share I would appreciate hearing more.

  31. This does not work at all in Excel 2007- I replicated her data exactly as she has it and it returns #N/A only..

    1. John, I’ve uploaded a sample file that you can use for testing. The link is at the end of the article.
      This is an array formula, so be sure to press Ctrl+Shift+Enter instead of just pressing Enter, after you type the formula.

      1. I used this formula on my data. It works fine for cells that has the text we are trying to search but for others it show “#N/A”
        Not sure what is wrong! Can you please help?

  32. Hello Debra,
    Thank you for your post. In hope that you or one of your readers could help me, I would like to ask you the following:
    I have two lists:
    list 1 contains first and last name; example: Chris Sampleman
    list 2 contains a code made up of characters from first and last name (and other characters); example: chrwosamdt
    Both lists are of different lengths. I am trying to match up first and last name of List 1 with the code of List 2, with the formula returning the code (or closest match) of List 2. The code in List 2 contains some (not all) characters found in the first and last name of List 1, but not necessarily in sequential order.
    How could I find if individual characters of a cell within a range are embedded in the cell of another range, with a formula to return the content of the cell that matches closest?
    If a formula does not work, is there another way to do this? I tried your formula, name abbreviations and combinations, various VLOOKUP versions, and even a “Fuzzy Lookup” add-on to Excel.

  33. Dear Debra,
    Greatful for your solution, I run into the following query:
    For a table with 6200 records and 31 arguments to match, the formule =IFERROR(INDEX(checklist;MATCH(1;COUNTIF(B10;”*”&checklist&”*”);0));”?”) works fine. The checklist represents a column of arguments to match with. However how do I extract multiple strings, i.e. type1, type 2 from a row, for example
    “machine part for type 1, type 2, etc”, as for now it just extracts randomly one value, either type 1 or type 2?
    What I would like to achieve is, to find all the arguments and have them then listed sequentialy.
    Thanks for your support.
    Gerard

  34. Hi, I’m looking to build a formula in excel that searches an array of cells for a string of text/multiple words, and picks the cell from the array with the best match (or most number of words from the string of text that matches.
    Using the first example above, it would be finding the cell in column A that contains dog and cat and pig (if one existed), without triggering a match if only one or two of the words matched, and then returning a code located in an adjacent cell (in column B for example, if column B contained a unique code for each cell in column A).

  35. Thank you very much Debra. I was actually going to code some VB script, before I came across your super-helpful blog. Really liked your break-up based explanation using countif, match, index, and iserror functions separately. The power of Excel functions and their combinations continues to amaze me and it is professionals like yourself who bring this to light. Keep up the great work!

  36. Excellent Blog, but I am having issues with reproducing the formula in my environment. I have a column with a range of data and I might need to search on one or more words. Below is an example of the data I have to search through:
    test123
    INTEREST PAID
    Deposit 123.23 Westpac
    The above is in cell A1. The formula in B1 is: =COUNTIF(A1,”*”&D1:D3&”*”). Using $d$1:$d$3 doesn’t make a difference. The data I am using for the keywords located in d1 to d3 is:
    test
    westpac
    Interest paid
    Formula in B1 generates a true and B2 to B3 generates a failure (0). I am creating an array formula. This is all in Excel 2016 365. Any ideas why this is occurring?

  37. Hi,
    can you help me. i want to get the SI#0372 in a cell.
    This is the cell context and the text to find, but add a little number such as this below.
    Remarks: ITM Reference: SI#0372 DR#0472 Assignment: 0001000031 SI#
    i have a formula which found from this page and it gets only SI#

  38. hi please help me
    just want to get a text from a sentence
    a1:ITM Reference: SI#0372 DR#0472 Assignment: 0001000031
    a2:SI#
    a3:=IFERROR(INDEX($C$8:$C$11,MATCH(1,COUNTIF(B8,”*”&$C$8:$C$11&”*”),0)),””)
    result is SI#
    However, i’d like to get the 0372 hence result should be SI#0372
    Hoping to find solution from you po. Many Thanks

  39. Hi Debra,
    Your solution almost gets the result I need. I’m hoping you could help find the general solution as I suspect others will find it very useful.
    I have two named-ranges to make it easier; Net_List is the first column of Net_Rank and consists of a list of Networks e.g. Wifi, Eth, BT, LoRaWAN etc. Adjacent to this list of Networks is their ranking number e.g. 4, 1, 2, 1 etc. These ranks are changed by the user to suite business priorities.
    The source data is a single cell C14 with the string “Eth | Wifi | GSM | LoRaWAN”. The goal is to find all matching Networks in the string and add up their ranks. So in this case, the manually calculated result is 1+4+2+1=8. The array-formula I have only finds the first match being “Wifi” and indexes the first rank value being “4”:
    {=INDEX(Net_Rank,MATCH(1,COUNTIF(C14,”*”&Net_List&”*”),0),2)}
    The array produced by the COUNTIF is {1,1,0,1,1,0,0,0,0}, so this is finding all Networks with their position in Net_List. MATCH only finds the first match to “1” in the array that corresponds to “Wifi”, hence its rank in the complete formula. I know of using SMALL to get first, second, third etc. instances but this only works line by line. Is there a possible solution where the matching ranking values can be added in one array formula?
    Many Thanks,
    Michael.

  40. Hello I was hoping you might be able to assist me. I am using this formula however my reference has blank cells. I am noticing that this formula will not search past the blank cells. How can I fix this?
    Thank you!

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.