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.

vlookup partial match first 5 characters

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.

Apply the Formula to the Rest Cells

How to Vlookup a Partial Match for the Last 3 Characters

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

How to VLOOKUP Partial Match Last 3 Characters

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.