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.
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.
Read More: How to Use VLOOKUP to Find Partial Text from a Single Cell
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.
Read More: Excel VLOOKUP for Partial Match in Table Array
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.
Download Practice Workbook
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
- Use VLOOKUP to Find Multiple Values with Partial Match in Excel
- How to Use Excel VLOOKUP to Find the Closest Match
- How to Use VLOOKUP to Find Approximate Match for Text in Excel
- How to Perform VLOOKUP with Wildcard in Excel
<< Go Back to VLOOKUP Partial Match | Excel VLOOKUP Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
