How to Vlookup Partial Match for First 5 Characters in Excel

Get FREE Advanced Excel Exercises with Solutions!

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 to VLOOKUP partially matches the first 5 characters.


Download Practice Workbook


Why Should We Use VLOOKUP Function for Partial Match?

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

vlookup partial match first 5 characters


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.

Insert the VLOOKUP Formula in a Cell

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.

Read More: [Fixed!] Excel VLOOKUP Partial Match Not Working


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.

Apply the Formula to the Rest Cells

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


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 F Column in the C Column.

How to VLOOKUP Partial Match Last 3 Characters

Steps:

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

vlookup partial match first 5 characters

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.

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


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 for valid output.

Conclusion

We can find the first or last characters of a range if we study this article properly. Please feel free to visit our official Excel learning platform ExcelDemy for further query.


Related Articles

Shajratul Alam Towhid
Shajratul Alam Towhid

Hello, myself Shajratul Alam Towhid. Basically, I am a Naval Architect who wants to expand knowledge in the field of Microsoft Excel. I wish all of my articles will be beneficial for the readers.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo