Tutorial: Use a VLOOKUP for part of a string or part of a word

You can use vlookup to look at part of a string by combining it with other Excel functions.

For example, we have a list of product codes which look like this.

Product codes table for vlookup

 

 

 

 

 

 

 

The last two letters of each code represent a country, and we have a country codes table that looks like this.partial match vlookup example pic 2

 

 

 

 

 

 

In order to populate our first table with countries, we need to do a VLOOKUP on the last two letters which we’ll do using the RIGHT function. So the SearchFor part of the formula becomes RIGHT(A2,2) which means the rightmost two letters of the value in cell A2 which in this case is DE. The rest of the VLOOKUP formula is just done as usual.

=VLOOKUP ( SearchFor , WhereToSearch , WhichColumn , NearOrExact )

partial match vlookup example pic 3

 

Resulting in the countries column of our product table being populated with countries!

vlookup right formula result

 

 

 

 

 

 

 

Want to get good at using the VLOOKUP function?

Download our practical VLOOKUP tutorial workbook with step-by-step instructions and examples to help you become a VLOOKUP pro.

Leave a Reply

Your email address will not be published. Required fields are marked *