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.
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.
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)
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))
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.
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.
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
____________________________
Hello Debra
Without CSE and shorter:
=IFERROR(LOOKUP(2,1/SEARCH($D$1:$D$3,A1),$D$1:$D$3),"")@Detlef, thanks, very nice! You reminded me that I used a similar formula in the 30 Excel Functions series, and Example 2 on this page explains how your formula works:
http://blog.contextures.com/archives/2011/01/17/30-excel-functions-in-30-days-16-lookup/
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
@Paula, thanks, and you could send me your file if it's not too big. The address is
ddalgleish
@
contextures.com
@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/
How can I count the number of times a specific word or text string appears in the COMMENTS of a spreadsheet?
Jorge
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
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
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.