Spreadsheets at Depth

Looking a value up in a table

Read a VLOOKUP: find the row its search value matches, and take the answer from the column its number names.

Two sheets of the same information turn up constantly: a long list of what happened, and a short table saying what each thing is. A till roll says pen, pen, bag; a separate table says what a pen costs. Copying the prices into the till roll by hand is where mistakes come from, and it has to be done again every time a price changes. VLOOKUP does the joining instead. It is given the value to look for, the range the table sits in, and a number saying which of the table's columns to take the answer from — and FALSE on the end, which is what asks for an exact match rather than a near one.

It searches the first column, and counts columns from the table's own edge

Both halves of that catch people out. A lookup only ever searches the leftmost column of the range it was given, so the thing you are looking for has to be in that column and nowhere else — a table with the names in the middle cannot be searched by name at all. And the column number is counted from that same left edge, starting at 1: in a table sitting in C1:E4, column C is 1, D is 2 and E is 3, whatever the letters on the sheet say. That the counting starts at 1 here, when a list's positions start at 0, is not a rule anybody can reason their way to. It is two different tools built by different people, and the only way through it is to notice which of them you are using.

Worked example

What is wrong with this lookup, and what does it show?

=VLOOKUP("bag", A1:B4, 3, FALSE)

  1. The range is A1:B4, which is two columns wide — A and B, and nothing else.

    Reading the range first, before anything else in the formula, is worth making a habit of. It settles both what can be searched and what can be answered with.

Try it together

Now build a lookup rather than read one. A table of pupils sits in D2:G30 — names in D, house in E, marks in F and prizes in G — and you want the marks belonging to one particular name.

Take the three parts in the order the formula asks for them, and let the range settle the last one.

    1.Which column letter must hold the names, given that this table can be searched by name at all?

    Have a go

    Have a go on your own. A table sits in A1:C3. Column A holds red, blue and green; column B holds 8, 3 and 12; column C holds 40, 55 and 20. What number does this formula show?

    =VLOOKUP("blue", A1:C3, 3, FALSE)

    Ready to practice?

    Eight questions on what you have just read. Nothing is timed, and you can play as many times as you like.

    Print a worksheet