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

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.

Download the Practice Workbook

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.

INDEX and MATCH Functions for Partial Match Using Wildcard

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.
  • To explore more about this function, you can visit this link
  • 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.
  • To learn more about the INDEX function, you can look at this link

Enter formula using INDEX and MATCH Functions

Step 2: Now enter any keyword in cell D14 and press Enter

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.

index match partial match

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

Formula using INDEX and MATCH functions

Step 2: Now enter any keyword in cell D14 and press Enter

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.

index match partial match

Step 1: Enter the following formula in cell D16 and press Enter

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

Enter formula using INDEX and MATCH functions

Step 2: Now enter any keyword in cell D14 and press Enter

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.

Retrieve Numerical Data Using INDEX and MATCH Functions 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))

Enter formula using INDEX and MATCH functions

Step 2: Now enter any keyword in cell D14 and press Enter

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.

VLOOKUP Function for Partial Match

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.
  • For more information, you can visit this link

Formula using VLOOKUP function

Step 2: Now enter any keyword in cell D14 and press Enter

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.

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

ExcelDemy
Logo