Lookup Tables

Lookup tables are used to locate a piece of data corresponding to the reference input value. It is useful in cases where there is a large volume of data to sort through, such as in a school or company setting.

Hlookup

Used for tables with row headers (i.e. each row begins with a header). It searches for a specified input value from a row, and then returns a value from a different specified row in the same column.

The standard formula displayed in Excel is as follows:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

This can be elaborated as:

=HLOOKUP(What you are looking for, where to look for it {select the whole table}, which row number the output value is located, use an Approximate or Exact match {1/TRUE or 0/FALSE, respectively}).

Vlookup

Used for tables with column headers (i.e. each column begins with a header). It searches for a specified input value from a column, then returns a value from a different specified column in the same row.

The standard formula displayed in Excel is as follows:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The standard formula can be elaborated as:

=VLOOKUP(What you are looking for, where to look for it {select the whole table}, which column number the output value is located, use an Approximate or Exact match {1/TRUE or 0/FALSE, respectively}).

The difference between an approximate and exact match is that the former allows room for input errors/inaccuracies (e.g. typos, as seen in the vlookup example where the name “Chloe” was mistakenly spelt with an extra “y” at the end) while the latter requires an exact character for character match to return a value.

Sort Filters

Sort filters allow you to organise a set of data in a meaningful way by arranging it in a specific order based on the given criteria (e.g. alphabetically, numerically), making it easier to analyse and read large pieces of data. In the example below, the “Score” column has been sorted in descending order (largest to smallest) to determine the class rankings.