How to Use INDEX and Match for Partial Match (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

index match partial match


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.

Match Starting Characters

Formula Explanation
  • 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))

Retrieve Numerical Data by Combining INDEX and MATCH Functions for Partial Match with Multiple Criteria

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

Retrieve Numerical Data by Combining INDEX and MATCH Functions for Partial Match with Multiple Criteria


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.

An Alternative Way with VLOOKUP Function for Extracting Data with Partial Match

Formula Explanation
  • 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.
Notes
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.


Further Readings

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdullah Al Murad
Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo