[Fixed!] Excel VLOOKUP Partial Match Not Working

One of the most powerful functions in Excel is the VLOOKUP function. It helps to find any specific data or value from a large dataset. Even it works for both exact and partial matches. But sometimes we face the problem when the VLOOKUP partial match does not work in excel. There are several reasons for this. In this article, we will find out why the excel VLOOKUP partial match not working from 5 reasons with solutions.

Download Practice Workbook

Get this sample file to practice by yourself.

What Is Partial Match in Excel VLOOKUP?

In general, when the VLOOKUP function is associated with Wildcard Characters (*,:, &), they find partial matches from the dataset. This means, if your search data is not exactly the same as the source, it will still find it from the keywords.

5 Reasons with Solutions When Excel VLOOKUP Partial Match Is Not Working

To get into the root of the problems and find solutions, we prepared a dataset here. The dataset shows the information of 5 types of Product Names with their Product ID and Sales Amount in the Sales Report-August,2022.

From this dataset, we will find the Sales Amount based on the search data Cleaning.

Let’s see what problems we face during this process. Also, we will provide solutions for them.

1. Wrong Placement of Wildcard Characters

Wildcard characters are the most important element in the VLOOKUP Partial Match formula. But what happens when it is not placed correctly? Let’s check it out.

• First, insert this formula in cell 12.

• Then, press Enter.
• Here, you will see that the output is showing #VALUE!.

This error occurred because we inserted the Asterisk (*) in the wrong position. Also, we did not provide all the required Wildcard Characters.

Solution:

• To fix this, insert this formula in cell C12 instead.

• Lastly, hit Enter and you will finally see the Sales Amount for the Search Data “Cleaning”.

Read More: How to Vlookup Partial Match for First 5 Characters in Excel

2. False Column Number Reference in Excel Formula

Another reason for VLOOKUP Partial Match not working is referencing the wrong column number. Let’s see how it happens and how to solve it for the same search data.

• First, insert this formula in cell C12.

• Next, press Enter.
• As you can see, the Output is showing #REF!

This problem occurred because we entered the column number as 4 in the formula. Though alphabetically, you may think that as the Sales Amount is in column D, it will be the 4th column. But according to the dataset, it is the 3rd column.

Solution:

• Therefore, insert this corrected formula.

• Lastly, hit Enter and your problem is resolved.

Read More: VLOOKUP Partial Text from a Single Cell in Excel

3. Mismatch Between Search and Source Data

It is a very crucial factor if your search data mismatches with the source one. Following is an example of this error.

• Here, we typed Stationaries as our search data in cell C11.
• Then, applied this formula to cell C12.

• Next, hit Enter and you will see that it shows #N/A.

So, what made this happen? It is because we typed Stationaries instead of Stationary as the search data. Though they sound familiar, VLOOKUP will only look for a proper match. Therefore, despite of inserting the correct formula, it is showing this error.

Solution:

• So finally, insert the word Stationary and you will see the sales amount of this.

Read More: VLOOKUP Partial Match Multiple Values (3 Approaches)

4. Extra Space Inside Excel VLOOKUP Formula

At the time of typing any formula, we need to be very careful of the spacing between symbols and cell numbers. Let’s see how ignorance in this factor reacts in VLOOKUP Partial Match.

• First, insert this formula in cell C12.

• Next, hit Enter.
• Following, you will see the cell is showing #N/A.

Solution:

• Therefore, remove unnecessary spaces and insert this formula instead.

• Finally, press Enter and you will see the correct value.

5. Missing Search Data in INDEX-MATCH Formula

The combination of the INDEX-MATCH function is an effective alternative to the VLOOKUP Partial Match formula. It finds data with parts of letters from the source data. Let’s see what errors we face in this process for missing search data.

• First, insert the keyword Hm as the Search Data in cell C11.

• Then, insert this formula in cell C12.

Here, we used the INDEX function to return the value from cell range B5:B9. Then, applied the MATCH function to find a similar value of cell C11 in the selected cell range.

• Following, press Enter.
• Here, you will face this #N/A error because the letters Hm are not present in any of the initial letters among the source data.

Solution:

• So, type Hom instead and you will find the actual output.

Read More: How to VLOOKUP Partial Text in Excel (With Alternatives)

Conclusion

So far, hopefully, we covered all the issues regarding excel VLOOKUP partial match not working through 5 reasons with solutions. Let us know if you face any other difficulties. Follow ExcelDemy for other excel blogs.

Related Article

• Excel VLOOKUP for Partial Match in Table Array (3 Examples)