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 :
This will return TRUE if the cell contains text and false if it does not contain text.
Or you could use
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 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 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.
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 6th 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.
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.
Finally, we have selected exact match which is displayed as a FALSE.
Copy and past the following into an Excel workbook.
The stock codes can be broken down as follows:
The first 3 characters represent the Supplier. These are the left most characters
The forth and fifth characters represent the Area code in the warehouse
The last 3 characters represent the name of the product. These are the right most characters.
We wish to pull in the Area for each stock item.
Our lookup tables contain a code and an area. This code is the area code contained with the stock code. The area is the field we wish to return, and it represent where the stock items are in a warehouse.
Prepare a lookup that will use the stock code to lookup and return the area where the stock item can be found in the warehouse as per the image below.
To earn STEEM tokens for this blog post, in the comments section below answer the following questions
- What text function is needed in this case?
- What is the full formula that you used?
Good luck with the activity – I look forward to seeing your comments and hearing how you got on trying this out.