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 and pretty much everything about it is replicated in a more straightforward way in the XLOOKUP.

VLOOKUP is one of the most commonly used functions in spreadsheets over the last half a century, and so while it may have been superseded by XLOOKUP we're going to need to take a look at it. Let's see what arguments go into the function:

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.

Probably the best lookup function in Google Sheets and Excel (2019 or after, and Microsoft 365) is 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 works:

XLOOKUP can work either vertically or horizontally, depending on the ranges selected. The return range can also be wider than a single column or row to return matches from multiple fields in your dataset.

MATCH and INDEX can be used together for lookup purposes, and were often favoured over VLOOKUP because they didn't rely on hard-coded numbers and column counting. This approach has been superseded by XLOOKUP but you might still come across it so let's take a look:

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:

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 chimera, 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…

5. As you add more items to your pivot table, you might want to fine-tune its appearance. A contextual Design tab will be available on the ribbon menu with various Layout options. These control aspects such as subtotals and repeated labels.

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.

5. Options for subtotals and repeated row labels appear in the side menu on the field 'cards' you add. You will also be able to sort your data using the sort controls on these cards.

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.

select

select selects the columns you want to display. These columns can be expressed by their letter names or by their position in the data array (expressed as Col1, Col2, etc.), so...

=QUERY(dataList, "select A,F,B")
…is the same as…
=QUERY(dataList, "select Col1,Col6,Col2")

The selected columns can be in any order. They just need to exist in the original data, so if the named range dataList is mapped to columns A:F then you couldn't refer to a column G or a Col7 in your query expression.

order by

order by controls the way your data is sorted — for example:

=QUERY(dataList, "select A,F,B order by B desc")
…or…
=QUERY(dataList, "select Col1,Col6,Col2 order by Col2 desc, Col3 asc")

asc orders the data in ascending order by the column specified; desc in descending order. The columns controlling the sort do not need to be the columns select‑ed for display but do need to be available in the data array.

Using "where" for equalities, inequalities, and exact matches

where allows you to filter your data. As with standard functions, numbers and text require slightly different syntax:

• As usual, numbers don't use quotes — where A=42
• Text values are enclosed in 'single quotes' (so as not to interfere with the "double quotes" of the query expression) — where A='Donkey'
• Dates often require their own special syntax but we'll get to that!

Here's a couple of simple examples:

The usual symbols for equalities and inequalities apply:

Because the query expression is a string of text in its own right (encased in "double-quotes" in the QUERY formula), it's necessary to 'break out' of the string to reference any cell values in your spreadsheet. The concatenation operator & is used to join the cell reference to the rest of the query expression:

The latter example needs two &s because it needs to add the final 'single quote' to enclose what is in effect a text string within a text string.

Let's assume that cell E2 contains the word Donkey — then…

=QUERY(dataList, "select A,B where F='Donkey'")
…is fundamentally identical to…
=QUERY(dataList, "select A,B where F='"&E2&"'")

Using "where" to match a text fragment

If you want to use only part of a text value in the where clause, use contains instead of = :

=QUERY(dataList, "select A,B where F contains 'br'")

This would filter for all instances of text containing the string br, returning matches for broken, library, and cobra.

Using "where" with dates

Working with dates using the where command can be fiddly. It requires a precise form of syntax:

=QUERY(dataList, "select C,B,E,H where H < date '2005-03-26'")

Notice that:

• The word date must be used as an identifier in the expression
• The date is a text string (enclosed in 'single quotes')
• The date must use the format ‘yyyy-mm-dd’ with a hyphen separator

This creates an interesting challenge if you want to reference a cell value in the expression: if you enter the desired date into your cell in the ‘yyyy-mm-dd’ format, Google Sheets is likely to recognise it as a date and convert it to a ‘proper’ date/time value - which won’t work in the query expression.

To be sure of getting a correct ‘string’ value for the date, enter the date in your usual format and then use functions to generate the string. Suppose the reference date is in cell A1 — so long as it's a recognised date in Google Sheets you could use the TEXT function to convert the date to the appropriate text string:

=QUERY(dataList, "select C,B,E,H where H < date '"&TEXT(A1,"yyyy-mm-dd")&"'")

The file, More Query function examples, includes this and some alternative approaches on the where (dates) tab.