Tutorial: Doing a VLOOKUP for a word in a string

By | November 26, 2015

You might have a number of words in a string but you’re only wanting to lookup on one of them – the first word.

An example: You have a load of product descriptions which all start with the brand of the item. You’d like to include information about the product’s origin based on the brand.

Here is the original list. We want column B to show the location:

vlookup first word example

 

 

 

 

 

 

 

 

We have the following lookup list which shows the location of each brand’s origin:

So to find the first word, what we need to do is determine the position of the first space ” ” and to bring back all the letters to the left of that. This is done using a combination of the FIND and LEFT functions as below…

Using our usual VLOOKUP structure

SearchFor LEFT(A2, FIND(” “, A2) -1)

WhereToSearch E:F

WhichColumn 2

NearOrExact FALSE

We then copy that down the column to populate the other brands’ origins and we’re done!. You can download the VLOOKUP first word example to follow along

vlookup first word tutorial

 

 

 

 

 

 

 

 

 

 

 

 

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 *