As September approaches, I can count on a series of spreadsheet questions. One of the more popular Excel tutorial requests is how do you look up a value on one Excel worksheet and use it on another Excel worksheet. For example, you need to translate a product number into a product name. One of my favorite Excel functions is the VLOOKUP function and it can help with this task.

A recent case involved some voter registration data I needed to analyze. On one Excel spreadsheet, the voter’s party was listed as an alphanumeric value called “Pcode” and not the political party. This coding wasn’t intuitive. For example, “D” was for “American Independent Party”, but some thought it meant “Democratic Party”.

How can your grab a value from a separate Excel list and insert it into your current calculation?  Vlookup or Hlookup is the main answer in Excel.

We’ll show you Vlookup since it’s the most commonly used of the two.

Start with a reference table (Excel calls this the table_array) – this is merely a small table (either part of a worksheet or on a separate worksheet) that has a list of paired values.  For example:

• Sales targets paired with commission rates.
• Quantity purchased with discount rate
• State with sales tax rate
• Miles flown with frequent flyer points earned.

Once you have that (relatively) fixed information, you can use Vlookup() to find the relevant part of the table and get the value you need:

• Lookup total sales and get the commission payable
• Lookup quantity bought and get any discount that applies
• Find the customers state and workout if sales tax is chargeable
• From the air-miles work out how many points you’ll get.

The VLookup  function is a member of the Lookup and Reference functions group available in Excel.  It is a very useful function particularly where you have a list of data and further information can be derived from that list by reference to a lookup table.  The following examples give a practical illustration of how VLookup can be used.

Overview

VLookup(lookup_value, table_array, col_index_num, range_lookup) has 4 arguments:

• Lookup_value is the value you supply in your list, e.g Sales..  VLookup compares the lookup value with the values in column 1 of the lookup table array.  Lookup value is also called the “search” value.
• Table_array is the address of the lookup table itself, e.g. $E$2:$F$5, (I use absolute addressing so when I fill down the VLookup formula, the table address doesn’t move – creating wrong answers).
• Col_index_num specifies which column of the lookup table contains the data for use in our calculation, e.g. Comm.Rate% which is column 2 in the table.
• Finally, the range_lookup value is a logical value (TRUE/FALSE) which determines whether VLookup returns an exact match or an approximate match.  More detail on the range lookup value further on

The first example uses some familiar Northwind data and shows a simple example of calculating sales commissions based on employee sales.  VLookup gives the commission rate to be applied to the employee’s sales results.

The lookup table is the small list on the right – Min.Sales and Comm.Rate.  Vlookup always uses the first column of the lookup table as the reference for calculation, i.e. using Sales as the lookup value it compares Sales with Min.Sales and decides which Comm.Rate applies.

The Vlookup formula in cell C2 looks like this:

=Vlookup(B2,$E$2:$F$5,2,TRUE)*B2

The formula breaks down as follows:

• uses  B2 (sales) as the lookup value, (fairly straightforward),
• we want to calculate sales commissions so our reference table array $E$2:$F$5 is the basis for that.
• we want to grab the sales commission rate and that’s in the second column so we use the column index number 2 which means that VLookup will look in column 2 of the table array to return the corresponding commission rate.
• We want to find the sales value, and range_lookup is TRUE.  VLookup searches column 1 of the table array with the lookup value.  It aims to find the highest value of column 1 that is less than or equal to the lookup value, i.e. Sales in column B.

That last parameter, Range lookup, can be tricky.  There are three possibilities for this value.

• 1. TRUE  means VLookup will look for an approximate match
  2. FALSE means VLookup will look for an exact match
  3. value omitted means VLookup will look for an approximate match.

The smart play is to explicitly use TRUE or FALSE, until you are used to how it works.  How do these values work?

• FALSE, means an exact match, mainly used with text values.  VLookup simply looks down column 1 and looks for an exact match if it doesn’t find one it returns #N/A.
• TRUE instructs VLookup to find an approximate match (it is also OK if it finds an exact match).  VLookup looks for the highest value that is less than or equal to the lookup value.

IMPORTANT: If range lookup is TRUE the first column of the table array needs to be sorted in ascending order, from small to large.  (Otherwise VLookup may return unexpected results).

In our example Sales for Steven Buchanan is $22,000.  VLookup takes that value and goes down “Min.Sales” looking for an exact match, if there is no exact match (which there isn’t) VLookup looks for the largest value that is less than (or equal to) the lookup value.  In this case cell E3 is the largest value ($15,000) which is less than the lookup value ($22,000), therefore the VLookup function returns F3 which is a “Comm.Rate” of 10%.  Notice that the last term in our formula is *B2.  This multiplies the sales for each person by the commission rate found by VLookup.  Thus the commission for Steven B is $22,000 * 10% = $2,200.  If we then use the fill handle to copy the formula down to cell C10 we will have calculated the commissions for all the Northwind staff.

Copying the VLOOKUP Function to Other Cells

It doesn’t make sense to use VLOOKUP for one cell in your Excel spreadsheet. Instead, I want to copy the function to other cells in the same column.

To copy VLOOKUP to other column cells,

1. Click the cell containing the VLOOKUP arguments. In our example, this would be D2.

2. Grab the cell handle that displays in the lower right corner.

3. Left-click and drag down the cell handle to cover your column range.

Note: If I hadn’t changed to absolute reference as mentioned in Rule 3, I would’ve seen my table array entry shift by one cell as we dragged down through the other cells.

VLOOKUP is a powerful Excel function that can leverage spreadsheet data from other sources. There are many ways you can benefit from this function. In this example, I used a 1:1 code translation, but you could also use it for group assignments. For example, I could assign state codes to a region such as CT, VT, and MA to a region called “New England”. And for the adventurous, you can use VLOOKUP in your formulas.