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