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.
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 & “*”)
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.
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)
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))
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.
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.
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.
____________________________
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
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.
Very very thanks for Find Text With INDEX and MATCH solution . So nice you.
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.
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.
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