Method 1 – Using Wildcard Combining with INDEX and MATCH Functions for Finding Data with Partial Match
1.1 Match Starting Characters (Value&“*”)
Steps:
- 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))- You will be able to get the Darkmans which is a partial match of Dar.
Formula Explanation
- 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.
- 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.
- The INDEX function returns the partial match.
1.2 Match Ending Characters (“*”&Value)
Steps:
- Select cell D16, and write down the following formula in that cell.
=INDEX($C$5:$C$13,MATCH("*"&D15,$C$5:$C$13,0))- Press Enter. Afterward, you will get your desired output of the INDEX and MATCH functions which are given in the below screenshot.
1.3 Match Any Number of Characters (“*”&Value&“*”)
Steps:
- Insert the following formula in cell D16 and press Enter.
=INDEX($C$5:$C$13,MATCH("*"&D15&"*",$C$5:$C$13,0))- You will be able to get The Cost of Living which is a partial match of Cos.
Method 2 – Retrieve Numerical Data by Combining INDEX and MATCH Functions for Partial Match with Multiple Criteria
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))
- You 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
Steps:
- Insert the formula in cell D16 and press Enter.
=VLOOKUP("*"&D15&"*",B5:E13,1,FALSE)- You will be able to get the Harvest which is a partial match of arv.
Formula Explanation
- Inside the VLOOKUP function, “*” (asterisk) matches the data with our wildcard characters. & (ampersand) concatenates the asterisk and our entered keyword.
- B5:E13 this is the range where we will search the data.
- 1 define column numbers.
- FALSE defines the exact match.
Notes
| Common Errors | When they show |
|---|---|
| Match Problem | There might be a matching problem when the dataset has conflicting data. Results will be based on the first matched data. |
| Asterisk (*) Matching Issue | If the asterisk (*) sign is not used properly, for example, at the front, middle, or back, depending on the requirements, there could be some matching issues. |
| #N/A in VLOOKUP/INDEX/MATCH | If the searched value is absent in the given dataset, all these functions will return this #NA error. |
Download Practice Workbook
Download this practice workbook to exercise while reading this article.
<< Go Back to Partial Match Excel | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
