Md. Abdullah Al Murad
In MS Excel, instead of seeking something with full text or keywords, we may have to use partial text or keywords to look up desired data and information. Where the lookup value will be returned based on the partial match. Excel provides various ways for this type of issue. In this article, I will show different ways to find partial matches using the INDEX and MATCH functions.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
2 Suitable Ways to Use INDEX and MATCH Functions for Extracting Values with Partial Match
Consider the following dataset. Let’s use this dataset to demonstrate what actions to take when a keyword is searched. We will use the INDEX and MATCH functions for partial matches in Excel. On the other hand, we will apply the VLOOKUP function for partial matches as well. Here’s an overview of the dataset for today’s task.
1. Using Wildcard Combining with INDEX and MATCH Functions for Finding Data with Partial Match
Let’s assume a dataset of book information with their ID, Name, Writer, and Price. Now our task is to find out the book name using partial search keywords.
1.1 Match Starting Characters (Value&“*”)
Firstly, we will see how we can match any number of characters after the start of the text using the INDEX and MATCH functions. Let’s follow the instructions below to learn!
Steps:
- First of all, type the following formula in cell D16 and press Enter on your keyboard.
=INDEX($C$5:$C$13,MATCH(D15&"*",$C$5:$C$13,0))- As a result, you will be able to get the Darkmans which is a partial match of Dar.
- First, let’s see the inner function which is MATCH. In the first argument, D15&”*” is the lookup_value which matches the data with our partial text in the Model column. $C$5:$C$13 is the lookup_array. 0 is used to define the exact match.
- Then in the INDEX function, $C$5:$C$13 is the range where we will find the index, and MATCH(D15&”*”,$C$5:$C$13,0) is the row_num.
- Finally, the INDEX function returns the partial match.
Read More: Excel INDEX MATCH If Cell Contains Text
1.2 Match Ending Characters (“*”&Value)
Firstly, we will see how we can match any number of characters before the start of the text. Let’s follow the instructions below to learn!
Steps:
- First, select cell D16, and write down the following formula in that cell.
=INDEX($C$5:$C$13,MATCH("*"&D15,$C$5:$C$13,0))- After that, press Enter. Afterward, you will get your desired output of the INDEX and MATCH functions which have been given in the below screenshot.
Read More: How to Use INDEX-MATCH Formula in Excel to Generate Multiple Results
1.3 Match Any Number of Characters (“*”&Value&“*”)
In this part, we will search books using keywords that could be the first or last part of the book name. Let’s follow the instructions below to learn!
Steps:
- Insert the following formula in cell D16 and press Enter.
=INDEX($C$5:$C$13,MATCH("*"&D15&"*",$C$5:$C$13,0))- As a result, you will be able to get The Cost of Living which is a partial match of Cos.
Read More: INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)
Similar Readings
- Excel INDEX MATCH to Return Multiple Values in One Cell
- [Fixed!] INDEX MATCH Not Returning Correct Value in Excel (5 Reasons)
- Examples with INDEX-MATCH Formula in Excel (8 Approaches)
- How to Select Specific Data in Excel (6 Methods)
- INDEX MATCH vs VLOOKUP Function (9 Examples)
2. Retrieve Numerical Data by Combining INDEX and MATCH Functions for Partial Match with Multiple Criteria
In this section, we will see how we can extract numerical data from the dataset using partial match with the INDEX and MATCH functions. Our task is to find out the Book’s Name, Writer, and Price using a partial match. Let’s follow the instructions below to learn!
Steps:
- To get the Book’s Name, type the below formula in cell D16 and press Enter.
=INDEX($C$5:$C$13,MATCH("*"&D15&"*",$C$5:$C$13,0))
- Insert the below formula in cell D17 to find out the partial match of the Writer name, and again, press Enter.
=INDEX($D$5:$D$13,MATCH("*"&D15&"*",$C$5:$C$13,0))
- Lastly, we will get the Price of the matched book. For that, write down the below formula in cell D18 and press Enter.
=INDEX($E$5:$E$13,MATCH("*"&D15&"*",$C$5:$C$13,0))
An Alternative Way with VLOOKUP Function for Extracting Data with Partial Match
In Excel, to search for or find something from any given dataset or table, we use the INDEX and MATCH functions combined. However, there is an alternative to doing this type of task using a single function named VLOOKUP. Now we will see how we can find or search for something by partial match using the VLOOKUP function. Again, the dataset will be the same, but the formula will be different here. Let’s follow the instructions below to learn!
Steps:
- Insert the formula in cell D16 and press Enter.
=VLOOKUP("*"&D15&"*",B5:E13,1,FALSE)- Further, you will be able to get the Harvest which is a partial match of arv.
- Inside the VLOOKUP function, “*” (asterisk) is used to match the data with our wildcard characters. & (ampersand) is used to concatenate the asterisk and our entered keyword.
- B5:E13 this is the range where we will search the data.
- 1 is used to define column numbers.
- FALSE is to define the exact match.
Read More: How to Use INDEX MATCH Instead of VLOOKUP in Excel (3 Ways)
| Common Errors | When they show |
|---|---|
| Match Problem | There might be a matching problem when there are conflicting data in the dataset. Results will be based on the first matched data. |
| Asterisk (*) Matching Issue | If the asterisk (*) sign is not used properly, like at the front, middle, or back based on the requirements. There could be some matching issues. |
| #N/A in VLOOKUP/INDEX/MATCH | If the searched value is not present in the given dataset, then all these functions will return this #NA error. |
Conclusion
These are some ways to find any element using a partial match with the INDEX and MATCH functions in Excel. I have shown all the methods with their respective examples, but there can be many other iterations. I have also discussed the fundamentals of the functions used. If you have any other method of achieving this, then please feel free to share it with us.
Further Readings
- Excel INDEX MATCH with Multiple Criteria (4 Suitable Examples)
- INDEX MATCH with Multiple Criteria in a Different Sheet (2 Ways)
- INDEX MATCH across Multiple Sheets in Excel (With Alternative)
- Sum with INDEX-MATCH Functions under Multiple Criteria in Excel
- Index Match Multiple Criteria in Rows and Columns in Excel
- Index Match with Multiple Matches in Excel (5 Methods)
- How to use INDEX & MATCH worksheet functions in Excel VBA
