How to Vlookup Partial Match for First 5 Characters in Excel

If you are looking for how to vlookup partial match for the first 5 characters, then you are in the right place. We generally use the VLOOKUP function for searching elements within a range or table. Sometimes, we need to find a specific number of characters from a range. In this article, weâ€™ll try to discuss how VLOOKUP partially matches the first 5 characters.

Why Should We Use VLOOKUP Function for Partial Match?

The VLOOKUP function fetches data from one table to another when the lookup value matches the value of the lookup table. So, even a small space or extra character will give an error value as output such as a not available (#N/A) error. If we want to avoid this error, we need to use a partial match of the lookup value technique with the VLOOKUP function.

2 Easy Steps to Vlookup Partial Match for First 5 Characters in Excel

Excel generally offers various ways to find values using the VLOOKUP function. However, we have limited ways to find specific characters using the VLOOKUP function. However, those ways are very handy to use.
If we want to find the first 5 characters or any number of first characters, we need to use the combination of the VLOOKUP and LEFT functions. Suppose we have a datasheet named Using VLOOKUP and LEFT functions. We need to find the first 5 characters from the cells of column header UPSC from Company B in the C Column.

Step 01: Insert the VLOOKUP Formula in a Cell

• Firstly, write the following formula in the C5 cell like this.
=LEFT(VLOOKUP(B5,\$E\$5:\$F\$15,2,0),5)

Here, B5 refers to 67587698 which is the first UPSC from Company A.

Formula Breakdown:

VLOOKUP(B5,\$E\$5:\$F\$15,2,0) â†’Â  finds the lookup of reference B5 cell where \$E\$5:\$F\$15 is

the range where we want to look for it.

Output â†’ 5467892345

LEFT(VLOOKUP(B5,\$E\$5:\$F\$15,2,0),5) â†’ finds the 5 characters at the left of the number

5467892345

Output â†’ 54678

• Secondly, press ENTER.
• Eventually, weâ€™ll get the first 5 characters of the first UPSC from Company B which is 54678.

Step 02: Apply the Formula to the Rest of the Cells

• Thirdly, use the Fill Handle by dragging down the cursor while holding the right-bottom corner of the C5Â cell as shown in the picture above.
• Consequently, weâ€™ll get all the first 5 characters of F Column as output.

How to Vlookup Partial Match for Last 3 Characters

If we want to get the last 3 characters, we need to use the RIGHT function with the VLOOKUP function. We again want to get the last 3 characters from the cells of the F Column in the C Column.

Steps:

• Firstly, write the following formula in the C5 cell like this.
=RIGHT(VLOOKUP(B5,\$E\$5:\$F\$15,2,0),3)

Formula Breakdown:

VLOOKUP(B5,\$E\$5:\$F\$15,2,0) â†’Â  finds the lookup of reference B5 cell where \$E\$5:\$F\$15 is

the range where we want to look for it.

Output â†’ 5467892345

RIGHT(VLOOKUP(B5,\$E\$5:\$F\$15,2,0),3) â†’ finds the 3 characters at the right end of the

numberÂ  5467892345

Output â†’ 345

• Â After pressing ENTER and using the Fill Handle weâ€™ll get all the outputs like this.

Things to Remember

• If we want the VLOOKUP function to work properly, we must have a proper lookup value, the same in the table and the formula cell.
• Otherwise, we will face errors while seeking valid output.

Conclusion

We can find the first or last characters of a range if we study this article properly. Please feel free to leave your suggestions and comments in the comment box.

Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Shajratul Alam Towhid

Md Shajratul Alam Towhid, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, holds a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep passion for research and innovation, he actively engages with Excel. In his capacity, Towhid not only adeptly tackles complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his unwavering commitment to consistently delivering exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF