# How to Vlookup a Partial Match for the First 5 Characters in Excel

## 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.

## Dataset Overview

Suppose we have a datasheet named Using VLOOKUP and LEFT functions. Our goal is to find the first 5 characters from the cells in the UPSCÂ column from Company B and display them in the CÂ column.

### Step 1 – Insert the VLOOKUP Formula

• In cell C5, enter the following formula:
`=LEFT(VLOOKUP(B5,\$E\$5:\$F\$15,2,0),5)`

Here:

• B5Â refers to the lookup value (e.g., 67587698, which corresponds to the first UPSC from Company A).
• \$E\$5:\$F\$15 is the range where we want to look for the value.
• The 2Â indicates that we want to retrieve the second column (which contains the relevant data).
• The 0Â specifies an exact match.

• Press ENTER.
• This formula will return the first 5 characters of the first UPSC from Company B (e.g., 54678).

### Step 2 – Apply the Formula to Other Cells

• Use the Fill Handle (drag down from the right-bottom corner of cell C5) to apply the formula to other cells in column C.
• This will give you the first 5 characters of the UPSC values from Company B for all relevant rows.

## How to Vlookup a Partial Match for the Last 3 Characters

If you want to retrieve the last 3 characters, follow these steps:

Steps:

• Enter the Formula:
• In cell C5, enter the following formula:
`=RIGHT(VLOOKUP(B5,\$E\$5:\$F\$15,2,0),3)`
• The breakdown remains the same as before.

• Â Press ENTER.
• This formula will return the last 3 characters of the corresponding UPSC value (e.g., 345).
• Apply the Formula:
• Use the Fill Handle to apply the formula to other cells in column C.
• Youâ€™ll get the last 3 characters of the UPSC values from Company B for all relevant rows.

## Things to Remember

• Ensure that the lookup value in the table matches the one in the formula cell for the VLOOKUP function to work correctly.
• Otherwise, you may encounter errors while seeking valid output.

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