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.
____________________________
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
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
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
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
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.
This does not work at all in Excel 2007- I replicated her data exactly as she has it and it returns #N/A only..
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.
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?
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.