Using Lookup Tables
It is often useful to be able to
include a table of fixed values in your spreadsheet so that you can look up
values from it, an example would be the table shown below which lists road tax
rates applying to different categories of vehicle emissions.
If we now have a worksheet
containing details of various vehicles we can look up the corresponding rate of
road tax from the lookup table. In
order to make these functions work correctly it is important to put the values
you want to match in the first column of the table and to sort these values in
If we are going to find a matching
value down a column and then look across the row for a figure we use the VLOOKUP function, if we are going to find the match across a row and
then look for a figure down that column we use the HLOOKUP function. In our
example the VLOOKUP function will allow us to look down column A to find
the value nearest to the one we are matching and then look at column B, C or D
to find the corresponding road tax figure.
The VLOOKUP formula takes the form:
Lookup_value is the value you
want to find in the first column of the lookup
is the range of cells containing your lookup table.
is the number of columns across the table that you want to take your result from
(2 for Diesel and 3 for Petrol in our example).
Range_lookup tells Excel how
accurately you want to match. If
you write TRUE in this cell, Excel will only find an exact match and will return
#NA! if it cannot find one. Leaving
the last argument blank or entering FALSE will find the nearest value that is
less than the lookup value.
is our spreadsheet with the VLOOKUP formulas displayed.
Notice how the value 3 is used for
the last argument for the petrol cars and 2 for the diesel cars so that the
correct columns value is returned. The
range_lookup argument is blank in this case so Excel finds the nearest match,
returning the figure for the lower category if an exact match cannot be found.
Here is the resulting data from
these VLOOKUP calculations.
Click here to
return to the Excel index.