# How to Use INDEX and Match for Partial Match (2 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.

## 2 Ways to Use INDEX and MATCH Functions for Partial Match

### 1. INDEX and MATCH Functions for Partial Match Using Wildcard

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.

#### i. “*”&Value

Firstly, we will see how we can match any number of characters before the start of the text. Step 1: Enter the following formula in cell D16 and press Enter

`=INDEX(\$C\$4:\$C\$12,MATCH("*"&D14,\$C\$4:\$C\$12,0))`

Formula Explanation

• First, let’s see the inner function which is MATCH. In the first argument “*”&D14&”*” this matches the data with our partial text in the Model column. \$C\$4:\$C\$15 this is the Model column range. 0 is used to define the exact match.
• Then in the INDEX function, \$C\$4:\$C\$12 is the range where we will find the index. The return result of the MATCH data will be considered as a row number. Step 2: Now enter any keyword in cell D14 and press Enter #### ii. Value&“*”

Firstly, we will see how we can match any number of characters after the start of the text. Step 1: Step 1: Enter the following formula in cells D16 and D17 and press Enter

For D16:

`=INDEX(\$C\$4:\$C\$12,MATCH(D14&"*",\$C\$4:\$C\$12,0))`

For D17:

`=INDEX(\$D\$4:\$D\$12,MATCH(D14&"*",\$C\$4:\$C\$12,0))` Step 2: Now enter any keyword in cell D14 and press Enter #### iii. “*”&Value&“*”

In this part, we will search books using keywords that could be the first or last part of the book name. Step 1: Enter the following formula in cell D16 and press Enter

`=INDEX(\$C\$4:\$C\$12,MATCH("*"&D14&"*",\$C\$4:\$C\$12,0))` Step 2: Now enter any keyword in cell D14 and press Enter ### 2. Retrieve Numerical Data Using INDEX and MATCH Functions Partial Match

In this part, we will see how we can extract numerical data from the dataset using partial match by INDEX and MATCH functions. Our task is to find out the Book’s Name, Writer, and Price using a partial match. Step 1: Enter the formula in cell D16 to D18 and press Enter

For D16:

`=INDEX(\$C\$4:\$C\$12,MATCH("*"&D14&"*",\$C\$4:\$C\$12,0))`

For D17:

`=INDEX(\$D\$4:\$D\$12,MATCH("*"&D14&"*",\$C\$4:\$C\$12,0))`

For D18:

`=INDEX(\$E\$4:\$E\$12,MATCH("*"&D14&"*",\$C\$4:\$C\$12,0))` Step 2: Now enter any keyword in cell D14 and press Enter ## Alternative Option

In Excel to search or find something from any given dataset or table, we use the INDEX and MATCH functions combined formula. However, there is an alternative to do 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.

### VLOOKUP Function for Partial Match

Again, the dataset will be the same, but the formula will be different here. Step 1: Enter the formula in cell D16 and press Enter

`=VLOOKUP("*"&D14&"*",B4:E12,1,FALSE)`

Formula Explanation

• “*” (asterisk) is used to match the data with our wildcard characters. & (ampersand) is used to concatenate the asterisk and our entered keyword.
• B4:E15 this is the range where we will search the data.
• 1, 2, 3, 4 are used to define column numbers.
• FALSE is to define the exact match. Step 2: Now enter any keyword in cell D14 and press Enter ## Things to Remember

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 issue.
#NA 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 partial match with 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 used functions. If you have any other method of achieving this, then please feel free to share it with us.  