Lookup from a list in Excel

Office for Mere MortalsLast updated: 17 July 2018

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

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.

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

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.