LOOKUP is a weird one. Superficially it looks like the perfect lookup function: a supplied lookup-value (e.g. ‘Muffins’) is looked up in one column, and the value is returned from the same row of another column. Here it is in action:

But there's a catch. And it's a big one. LOOKUP only works if the lookup range is sorted in ascending order. And if it can't find what you're after it will make a best guess based on where in your list the lookup value should come. If "Muffins" were to appear anywhere else in our list other than in its proper alphabetical place between the lemon drizzle and the sausage rolls, we'd get the wrong result being returned. And that's not good at all.

For that reason, LOOKUP is only really provided in spreadsheets as a legacy function. You're (slightly) safer using one of the others instead...

The function's fiddlier than it needs to be, so let's see a (familiar) example:

Some things to notice / be aware of:

• Our list of data is in columns A:D but we're using B:D as our lookup-array because VLOOKUP needs the matching to happen in the first column of that lookup-array.
• Column D is the fourth column in the sheet, and the fourth column in our data table, but it's the third column in our lookup-array: B:D, hence the 3 in our formula.
• Our data in B:B happens to be alphabetical, but we're still looking for "Muffins" so we still want an exact match from our lookup. We should therefore use FALSE in the last argument. If we use TRUE (or miss out the last argument entirely) and the "Muffins" entry wasn't in B:B, we'd get back a misleading 200 from the lemon drizzle instead (more on why in a moment).
• Because we're using FALSE, the values in column B could be in any order: they don't have to be alphabetical. But if there's more than one entry for "Muffins", only the correspondence from the first entry in the list would be returned.

Range lookups

Let's get to the bottom of this TRUE/FALSE business...

In the example above, the fourth argument was set to FALSE. This means that a calorie value is only returned if "Muffins" is found in column B. Otherwise the formula will return a #N/A error.

So why would we ever want anything else?

Suppose we're marking an exam:

Our lookup array in F:G gives a set of grade boundaries: "Fail" starts at 0, "E" starts at 40, etc. A table such as this allows us to do a range lookup: rather than looking for an exact match, we want the closest range value below the lookup value supplied. So we put TRUE for that fourth arugment in the VLOOKUP. Now, when we look up the mark of 68 from cell C2, our closest match below 68 is the 60 in row 5 which corresponds to the grade "C".

Range lookups are generally used with numerical data where an exact match is less likely to be a thing. Of course, as with the LOOKUP example we saw earlier, our lookup range needs to be sorted in ascending order for the range lookup to work properly.

When creating a range lookup array, you must include the lowest possible value – in the above example, for instance, a zero is needed, otherwise values below 40 would generate an error.

HLOOKUP

HLOOKUP works in exactly the same way as VLOOKUP but horizontally rather than vertically: it looks across columns for a match and then down rows for a corresponding value.

MATCH and INDEX can be used together for lookup purposes:

Let's go back to our list of desserts:

In E2 we're using MATCH to look for the value "Muffins" (from D2) in B:B, while requiring an exact match (0). The result is 6. We're then passing that result to the formula in F2 where we're looking in A:A for the sixth row.

In row 5 we've done the same thing but expressing the ranges from rows 2:7 rather than using whole columns. This gives a different value for the MATCH because the returned row number is the row in the range, not the absolute row number on the sheet. This is also true of INDEX which is why we still get the right answer in F5.

Of course, the MATCH and INDEX functions can be combined, with the MATCH nested inside the INDEX. For example:

Although it uses two functions instead of one, this MATCH and INDEX approach is probably the best method for lookups, not least because it doesn't involve the cumbersome hard-coded column numbers of VLOOKUP.

In Google Sheets and Excel for Microsoft 365 there's now an additional function: XLOOKUP...

Fundamentally, this works like LOOKUP, but defaults to the "exact match" method you'd see when using FALSE in a VLOOKUP. However, the three extra arguments to the function let you do some serious fine-tuning of how the lookup functions:

XLOOKUP can work either vertically or horizontally, depending on the ranges selected.

Let's consider an example:

Students have volunteered to take part in activities to raise funds for charity, and we want to analyse the proceeds to see which activities were most successful, which colleges raised the most, and so on.

We may be tempted to lay our data out like this:

It's a nice, useable layout: each student with their own row; columns for each activity; values entered for the amounts raised in each case...

The trouble is, that data's already been pivoted!

What does that actually mean? Well, let's say we wanted to tot up the amount raised by each college or yeargroup. It's going to take an awful lot of messing about because our amounts raised are in multiple columns. Really we want to have the amounts raised in a single "Amount" column. And that means we'll also need a corresponding "Activity" column. And that means we need to do something to our data that might seem counter-intuitive — we need to do this:

This may look odd, particularly the repetition, but it means that each row is a collection of separate data items including all relevant information for each instance of a student doing an activity. And the stuff we want to be able to measure (specifically the amount raised) is in the one easy-to-handle column.

Data structure rules

The rules we talked about regarding lists are doubly true here:

• Data should be entered down the page, each item occupying a new row, so that each row contains one instance of the value to be used in calculation (e.g. the amount raised in the above example)
• Each column should contain just one type of information (e.g. text, number, date), and each cell should contain just one value
• Enter column headings in one row at the top of the list – never use more, and never merge cells for labels
• Do not repeat attributes across several columns (e.g. month names) no matter how tempting it might be!
• Do not leave whole rows or columns empty (some blank cells are fine)
• Stick to one dataset per tab of a spreadsheet file

Planning the ideal data structure

Here’s one approach that may help you plan or check your data structure. We’ll continue to use the student fund-raising example.

First, identify the data that provides the individual values that are most important to your analysis — in this case, it’s the amount raised at a specific activity undertaken by a specific student.

Next, surround this with the collection of related attributes:

Also include ‘attributes of attributes’ if you are likely to make use of them. For example, college and year are technically attributes of the student, but since we are storing all the data in one table, these must also be regarded as attributes of the amount, and included with each value.

These attributes should then be the additional columns used in the data set, so each amount has a corresponding activity, student, college and year.

Lowest common denominator data

Pivot tables work by grouping like values together. So it will look at our data-set, spot three rows of David Joneses and start merging them. Which is all well and good if we've only got one David Jones in our student cohort. But perhaps we've got two of them (like the one from The Lower Third and the one from The Monkees). If we don't want these two entities mingling together into some frightening gestalt, we're going to need to provide some means of disambiguation. Perhaps the year and/or college would be sufficient to do that, or maybe we're going to need a unique student ID.

If you've inherited a dataset that just isn't pivotable, there are ways to get that data into a pivotable structure.

The easiest way of unpivoting is via Excel's Query Editor (more of which later, but this will serve as a tantalising glimpse ahead):

1. Assign a named range to your list of data;
2. Select any cell within your list
3. Choose Data > Get & Transform Data > From Table/Range —the Query Editor will open;
4. Select the columns containing the values you need to unpivot (in our example, the columns with the amounts);
5. Choose Transform > Any Column > Unpivot Columns;

6. Rename your unpivoted columns (they'll be called Attribute and Value by default) — select a column and go to Transform > Any Column > Rename (or just double-click the header);
7. Hit Home > Close & Load — your restructured data will be written to a new list on a new sheet;
8. This list will be in the form of an Excel Table: a special kind of list with its own set of conventions. If you'd prefer it to be a normal range, you can go to Table Design > Tools > Convert to Range. This will sever the link between this table and your original data, so if your original data is likely to change you might not want to do that.

Unpivoting in Google Sheets is a bit harder. There are a few methods you could use, all of which essentially involve building up a coordinate map of your data and then restructuring it in a new location, including this example using OFFSET, ROUND, MOD, COLUMN and ROW.

There's also the "Monkey Tennis" approach by player0 and Ben Collins which unpivots a sheet using this beautiful formula:

=ARRAYFORMULA( IFERROR( SPLIT( TRIM( TRANSPOSE( SPLIT( TRANSPOSE( QUERY( TRANSPOSE( QUERY( TRANSPOSE( IF( Sheet1!B2:Z<>"", Sheet1!A2:A & "🐒" & Sheet1!B1:1 & "🐒" & Sheet1!B2:Z & "🎾", )),, 500000)),, 500000)), "🎾"))), "🐒"), ""))

And with the arrival of the FLATTEN function there's now an even more elegant way to unpivot in Google Sheets:

=ARRAYFORMULA( QUERY( SPLIT( FLATTEN( Sheet1!A2:A & "🥞" & Sheet1!B1:1 & "🥞" & Sheet1!B2:Z), "🥞"), "Select * where Col2 is not null"))

1. Make a copy of your list on a new sheet and reduce it to an index column and the columns you need to pivot (in our case we'd have the students column as our index and the activities columns for unpivoting. This effectively gives us a header row, a header column, and our values;
2. Use the "FLATTENed pancake" formula (or the "monkey tennis" formula if you prefer) on a new sheet, making sure that it points to the sheet with your data on (the formula assumes it's called Sheet1). If you have more than 26 columns, you'll need to modify the B2:Z bit of the formula;
3. The formula basically reduces your list to a coordinate set (using some emoji delimiters) and then breaks it apart in its new form. You'll need to add new headers for your resulting unpivoted data;
4. In our case we need to add back the student attributes of year and college which we could do with a VLOOKUP (or similar formula) back to our original data.

A pivot table can consist of the following elements:

• A field that contains the values to be used in calculation
• A field to be used as labels down the left of your table (row labels)
• A field to be used as labels across the top of your table (column labels)
• A field to be used for filtering your data.

The only real essential there is the values, though values without any breakdown is just a sum total. So you'll probably want to attach some of your attributes to the values so that you can compare them. You might therefore want to consider which attributes you want to compare: one of these could become the row labels, another the column labels. A filter then allows you to limit the rows, columns and values by another field.

Taking the example we've been playing with so far, our values are the amount raised. We could compare individual students (as rows) against the activities they were involved in (as columns), thereby essentially recreating the layout of that original table (albeit minus the year and college):

The year or college could then, potentially be used as a filter for our data. Or maybe even two filters. We could even add more column labels or row labels. That way we really could rebuild that first table if we wanted to:

Here's the thing about pivot tables – the thing that makes them brilliant: it doesn't matter what you put where because it's like Lego — you can just keep trying different things in different places and see what works!

For instance:

Having first ensured that your data is pivotable, and that you have an idea as to what numerical values you're going to be working with, it's time to do some pivoting!

1. Select any cell in your dataset and choose Insert > Tables > PivotTable — the data range is indicated and a dialogue opens with the range already entered.


There's also a Recommended Pivot Tables option in the same place that may give you an idea of what’s possible with your data.


2. You will probably want your pivot table on a new sheet, but you can opt to put it on an existing sheet (in which case you'll need to define the position of the top-left cell of the area to be used).


If you're specifying a location, don't try to put your table any higher than row 3 of the sheet, because you need to make room for filter controls.

3. Choose OK and the empty framework for your pivot will be created.
4. Using the panel that appears on the right, drag fields from the list into the appropriate areas below. As you do this, the used fields will be ticked. Alternatively you can tick the required fields and have Excel decide where to put them (you can always drag to rearrange if you disagree with it).

The field you choose for the Values will usually (but not always) be numeric. For numeric data you can choose to display a Sum, Average etc., by choosing the drop-down and selecting Value Field Settings…

1. Select any cell in your dataset and choose Insert > Pivot table — the data range is indicated and a dialogue opens with the range already entered.
2. You will probably want your pivot table on a new sheet, but you can opt to put it on an existing sheet (in which case you'll need to define the position of the top-left cell of the area to be used).
3. Choose Create and the empty framework for your pivot will be created.
4. Using the Pivot table editor pane on the right, add fields in the locations you require using the Add buttons. The panel will also suggest options it thinks you might want to try.

The following examples just use the simple nine-row list of three students and their fund-raising antics:

Q1: Which activities generated the most money in each year group?

• Columns: year
• Rows: activity
• Values: amount

Q2: Which activities were most successful in each college?

• Columns: college
• Rows: activity
• Values: amount

Q3: How well did students in year 2 do at raising funds? — which activities raised most?

• Columns: activity
• Rows: student
• Values: amount
• Filter: year 2

If we were doing a proper job at this, our student labels would probably take up more than one column as we'd have a unique studentID and we'd probably divide up the forename and surname fields.

Q4: Which activities by year 2 students raised the most?

• Columns: none
• Rows: activity
• Values: amount
• Filter: year 2

We've sorted the values column in descending order. We can do things like that — we're allowed.

Q5: How many activities did each year-group undertake?

• Columns: year
• Rows: activity
• Values: activity summarised by COUNTA

While we'd generally be working with numerical data for our values, we can also do basic counts of textual information.