EXCELUsing Lookup TablesIt 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 ascending order. 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: VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) Lookup_value is the value you want to find in the first column of the lookup table. Table_array is the range of cells containing your lookup table. Col_index_num 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. Here 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 column’s 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. |
