Data Training Courses

Excel Lesson - Look up data

This lesson covers techniques to look up data. Many people use these techniques to copy data from one Excel table to another and to reconcile two sets of data. This lesson covers functions: VLOOKUP, XLOOKUP, and the INDEX/XMATCH pattern

Case Study Data

In the exercise, we use a fictitious tiny sample data set of patients admitted to a hospital. We assume this is a master list of all patients registered and that the each patient only appears once in our master list - the data quality is good in this respect. The NHS ID is a unique for each patient. The master list also contains data for the ward, tariff and admitted date. Our sample data looks like this.

NhsId	Ward	AdmittedDate	Tariff
ABC-1234	Dermatology	07/12/2024	50
ABC-7890	Day Surgery	08/12/2024	70
XYZ-1234	Day Surgery	09/12/2024	100
XYZ-7890	General Surgery	07/12/2024	20

We may have another list containing only the NHS IDs of some patients that are of interest to us. For this list we need to lookup up attributes like the Ward or Tariff from the master list

We look at some cases where the data provided is in a different format or shape. For example, in this dataset the lookup array, the NHS ID, is not the left-most column.

Ward	NhsId	AdmittedDate	Tariff
Dermatology	ABC-1234	07/12/2024	50
Day Surgery	ABC-7890	08/12/2024	70
Day Surgery	XYZ-1234	09/12/2024	100
General Surgery	XYZ-7890	07/12/2024	20

Possibly our data may be in a row-orientated horizontal layout rather than the more usual column-oriented, vertical layout

NhsId	ABC-1234	ABC-7890	XYZ-1234	XYZ-7890
Ward	Dermatology	Day Surgery	Day Surgery	General Surgery
AdmittedDate	07/12/2024	08/12/2024	09/12/2024	07/12/2024
Tariff	50	70	100	20

Notes

VLOOKUP and HLOOKUP are well known and popular functions used for column-oriented and row-oriented data respectively.

VLOOKUP is usually not the best function to use. It has limitations. For example:

it uses an inexact match by default, whereas most circumstances require an exact match
it assumes that the lookup array is in the left-most column - this is not always the case
VLOOKUP can only handle column-oriented data; the HLOOKUP is for row-oriented data (V stands for vertical, H for horizontal)

XLOOKUP is a more modern, flexible and simpler alternative to VLOOKUP because XLOOKUP

splits the lookup array and return array into two sepate arguments which makes it much more flexible
has the if_not_found argument which avoids the need to wrap the function with an IFERROR function to handle cases where the lookup value may not be found
can handle both column-oriented and row-oriented data
does an exact match by default

Formulas that use either VLOOKUP and XLOOKUP, or indeed any function, are more readable if the data that contains the lookup and return arrays is in an Excel table.

The INDEX/MATCH pattern is more flexible than even the XLOOKUP functions since it splits the lookup and return operations over two functions (XMATCH and INDEX respectively). It is especially useful for pivoted data where we need to lookup up a value in a matrix given the row and column headers.