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

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.

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.

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


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.

Read More: How to Use INDEX MATCH Instead of VLOOKUP in Excel (3 Ways)

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.

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

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

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo