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.

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)

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

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…

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

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

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?

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.

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.

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

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.

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

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.

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

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

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.

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

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!

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?

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

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.

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.

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.

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.

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

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!

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?

The result in B1 is TRUE because it matches the first item in the list. That’s why the full formula uses MATCH — it finds the “1” in the array of 1s and zeros.
If you click in the formula bar and press F9, you’ll see the array: ={1;0;0}
Take a look at the SUMPRODUCT formula in today’s blog post too — it might be closer to what you need:http://blog.contextures.com/archives/2017/05/18/excel-filter-to-match-list-of-items/

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

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?

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?