Office Watch

Office 2013

Office Mobile / iPad

Office 2010

Office 2007

Office 2003

Office XP

Office for Mere Mortals

Access

Email

Buying Office

Office 365

Winks

Office News Wire

Join us!

Our Ebooks

Mobile | PDA

RSS


Search

Command Finder


Microsoft Office Bookshop

About

Home




Lookup from a list in Excel

Vlookup scares too many Excel users but it should not. David Goodmanson shows how easy and incredibly useful it can be.

by Office for Mere Mortals

Bookmark and Share

  | Mobile | click for more article services     


 

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.

Excel - Vlookup Northwind image from Lookup from a list in Excel at Office-Watch.com
 

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. 

Hlookup

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:

Excel - Hlookup sample commission data image from Lookup from a list in Excel at Office-Watch.com
 

which would use a function like this:

=Hlookup(B5,$B$1:$E$2,2,TRUE)*B5

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.

Office 2013: the real startup guide

OFFICE 2013: the real startup guide Everything you need to know about Office 2013 but Microsoft won't tell you.

How to save money, install, configure and use the new features in Office 2013.  Get it today - click here.

Windows 8 for Microsoft Office users

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:



Article Services sponsored by: Office Watch Ebooks - available now to download and read today.
RSS feed for this category Subscribe

Translate | Mobile | Links
 Add to: Bookmarks | | DiggThis | Yahoo! My Web


New & Popular
» Using Conversations in Outlook
» OneDrive for Business alters files
» About Outlook Conversations
» Keep using your device on the plane
» Questionable Outlook holidays
» Office 365 Personal arrives


Office Watch, Office for Mere Mortals, Access Watch and all titles used within the publications are Copyright © 1996-2014 Office Watch.
Microsoft Office, Microsoft Word, Microsoft Excel, Microsoft Outlook, Microsoft Powerpoint and doubtless many other names are registered trademarks of Microsoft Corporation.

Search  |  Sitemap |  Popular Topics | Privacy Statement |  Advertising |  Twitter |  Feedback / Contact Us
Office Watch is definitely not affiliated with Microsoft - and that's just one reason why we are so useful to Microsoft Office users around the world J (Erko).