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.
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:
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.
- TRUE means VLookup will look for an approximate match
- FALSE means VLookup will look for an exact match
- 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.
Hlookup works the same a Vlookup except that it searches across a row instead of down a column.
That's possible but not as common as Vlookup applications.
Here's the same sales commission list in the different format:
which would use a function like this:
Alternatives to Vlookup and Hlookup
You don't have to use Vlookup - there are alternatives though they are nowhere near as flexible or easy.
- A complex set of nested IF statements could achieve the same result and in the early days of spreadsheets was needed for situations like this. But nested IF's are hard to debug (ie easy to get wrong), has to be copied into each cell and each individual formula has to be updated if there's a change in values.
- Make a VBA function with a CASE statement to work through the options. Adding VBA code to a worksheet can cause security problems (people are wary to open documents with code). Mind you, the CASE statement is easy to understand and sometimes VBA code might be necessary if the lookup has exceptions or complex rules.
Article posted: Thursday, 15 January 2009
there's more ...
If you liked this article you'll LOVE our new ebooks.
Windows 8 for Microsoft Office users A practical guide the new, changed and unfamiliar in Windows 8
A focused and unvarnished look at Windows 8, especially written for
the many people who use Microsoft Office Get it today
- click here.
ORGANIZING OUTLOOK EMAIL - tame your Outlook 2010 Inbox
100+ pages of practical tips and help to streamline,
automate and search your Inbox. Get more
than you ever thought possible from Outlook. Read it today
- click here.
More from Office Watch: