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.
How to Use INDEX and MATCH Functions for Partial Match in Excel: 2 Suitable Ways
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.
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.
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.
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.
| 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. |
Download Practice Workbook
Download this practice workbook to exercise while reading this article.
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.
<< Go Back to Partial Match Excel | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
