In a previous article we covered The Basics of VLOOKUP.  VLOOKUP is a very versatile function that can be combined with other functions.  When you learn to combine VLOOKUP with with other functions it makes it super powerful. In this article we will look at combining VLOOKUP with TEXT functions.

Lets quickly refresh some basics of VLOOKUP

The syntax for VLOOKUP is

VLOOKUP = (lookup value, table array, column index, range lookup)

Lookup value is the value you wish to look up.  This value must be in the far most left column of the table

Table array is the table in which you want to search.  The first column will always be the column that contains the lookup value. 

Column index is the column number you wish to return the data from.

Range lookup offers a true or false selection.  Where true is an exact match. This will only return a value where an exact match to the lookup value is found. When using an Approx. match (false), items in the table must be sorted from lowest to highest, or in alphabetical order.

VLOOKUP within TEXT – The Problem

Below we can see two sets of data.  The first contains the phone area code with the region and the second contains customer phone number.  We want to use the phone number to carry out a lookup and return the region.

Looking at the Customer Phone Numbers, the first 4 digits represent the area code.  These values are text.  We can recognise they are text because numbers are always aligned to the right of a cell and text is always aligned to the left. 

If you are unsure if a cell contains text or numbers, you can use :

=ISTEXT(cell reference)

This will return TRUE if the cell contains text and false if it does not contain text.

Or you could use

=ISNUMBER(cell reference) 

Which will return false if the cell contains text and true if it does not contain text.

As the customer phone number is text we will need to use TEXT functions to separate out the area code.

Useful TEXT functions for separating text

LEFT

Left will allow you extract characters from a string of text starting at the left most character. The syntax for LEFT is

=LEFT(text, Number of Characters)

where the number of characters is the number of characters you wish to extract from the string.

The customer phone number starts with the area code on the left and it make up the first 4 characters of the text string.  We could use LEFT to extract this part of the text string taking 4 to be Number of Characters as required by the function.

=LEFT(F3,4) will return 1200 as these are the 4 left most characters in the string.

RIGHT

Right will allow you extract characters from a string of text starting at the right most character. The syntax for RIGHT is

=Right (text, Number of Characters) 

Where the number of characters is the number of characters you wish to extract from the text string.

If there is a reason to extract the last x number of characters from a text string you would use RIGHT.  For example

=RIGHT(F4,3) would return 458 as these are the 3 right most characters in the string.

MID

The syntax for MID is

=MID (text, start number, number of characters) 

where the number of characters is the number you wish to extract from the text string. 

The start number is the character number you wish to start extracting characters from.

=MID(F5,6,3) will return 324. 

This formula will look for the 6^th character starting at the far most left and it will then return the next 3 characters. A space will be counted as a character.

When you use these TEXT functions to extract text from a text string, the data type returned will also be TEXT.  Again, we can see this as the data is aligned to the right of the cell. 

To overcome this and use the returned text function value as a number we must add 0 to the text formula.  This will convert the text to numbers.

Combining VLOOKUP with TEXT functions

Now that we are familiar with TEXT functions and also the basic VLOOKUP we can combine these to solve the problem.

As VLOOKUP requires a lookup value which in this case is contained at the beginning of a text string, we can use LEFT to extract the lookup value from the customer phone number.

=VLOOKUP(LEFT(F3,4)+0,$A$2:$B$13,2,FALSE)

This formula will first carry out the LEFT function to return the first 4 characters, which is our area code.

Next it will go over to the lookup table and it will return the second column.  Note the use of $ will lock in the cell references so that you can copy the formula down.

 (…,$A$2:$B$13,2,…).

Finally, we have selected exact match which is displayed as a FALSE.