Introduction

Lookups (lookup, vlookup, hlookup and index/match) are a very useful feature in spreadsheets to identify particular data and match it to relating data. Compared to other functions within your spreadsheet they can be tricky to get your head around. Once you get the hang of it though you will see that it's not actually that scary. Let's dive in and see what they are and how they work.

Outline

What lookups allow you to do is identify a particular row or column in a table then return any other piece of data from that row or column. This is an action that we as humans do all the time without even really thinking about it.

For instance if I want to catch a bus to somewhere I might look at data for a bus route which has multiple columns. The first might be when the bus leaves a particular bus stop and the second will be when it arrives at another bus stop. If I want to get to Park Lane before 5pm then I would look at the Park Lane column to find out which bus will get me there in time. Once I've identified the row with the bus I want I would then move over to the corresponding Regent St column to lookup the time that I need to be ready at the bus stop. I would also lookup the corresponding row in the Bus column to know which bus I need to catch.

We can do this quite intuitively without really even thinking about how we do it. Setting up the function in a spreadsheet to perform a similar task is a little bit more involved but once you understand the steps it's really not that bad.

Lookup

The lookup function allows us to look through a sorted row or column for a particular item. It may be used in two ways :

LOOKUP(<searchKey>, <searchResultArray>)

The searchKey may be either a number or text.

Let's look at an example :

Here we have a table with some data about players playing a game. We want to find out which player got a score equal to or immediately below 700.

The lookup is performed on the first column or row in the array only.

If the searchKey is not in the search list then the item that is immediately smaller in the list will be matched instead.

When using this method, the corresponding item in the last row or column will be returned.

Now let's look at the second way we may use this function :

LOOKUP(<searchKey>, <searchRange>, <resultRange>)

Let's say we instead want to look up the level that a particular player got to. Because we want to identify an item before the lookup value we will instead specify both the search and result ranges.

When using this method both the searchRange and resultRange must be only a single row or column

Another thing to consider here is that the search range and result range don't need to begin on the same row (though most of the time they will). It will work if they are different as long as they are the same length.

VLookup

VLookup stands for Vertical Lookup and it behaves similarly to Lookup but has a few key differences:

• It is limited to searching within a column of data (whereas Lookup can search columns or rows).
• VLookup can work on unsorted data whilst Lookup can only work on sorted data.

VLOOKUP(<searchKey>, <searchRange>, <index>, [isSorted])

Here is an example with sorted data. You will notice that we are actually looking up the value 5 however that does not exist. As the data is sorted, it can instead return the closest value that is closest to but not over 5.

If the data was unsorted instead, we can still do a VLookup on the data but now we may only identify exact values.

HLookup

HLookup stands for Horizontal Lookup and it behaves pretty much the same as Vlookup but with Horizontal (ie rows) data instead of columns of data.

HLOOKUP(<searchKey>, <searchRange>, <index>, [is_sorted])

Because most of the time our data is stored in vertical columns, this function is not used as frequently as VLookup.

In this example we are using HLookup to find a particular department and then return the corresponding cell 1 below.

Index Match

Here we are going to combine two functions, index and match. Let's build this up. We will look at the Match function first.

MATCH(<searchKey>, <searchRange>, [searchType])

Here we are simply using match to find out how far down the column the item "Squash" is. It is the 4th item down the list.

The index function allows you to specify a range of data and then a column and row within that data in order to specify a particular cell. It will then return the value stored in that cell.

INDEX(<range>, [row], [column])

The range and column are both optional and will default to 0 if left out. Under most circumstances however you will not want the zeroth item so you will need to specify a value.

Here we are simply using index to find out what is in the cell that makes up the 5th column and 2nd row of the given range of data.

Now let's combine index and match. To create a function with functionality similar to VLookup we will use index with the row field replaced with a match function.

In this example we have returned the meat that is in the same row as Green Bean.

If we wanted to perform functionality similar to a HLookup then it is simply a matter of moving the match function over to the column field in the index function.

Why bother?

So you may be asking yourself "Why bother with Index Match?". It seems to be more typing and more fields to worry about to achieve the same thing. Most of the time that is probably correct but there are a few things that the Index Match combo can do that VLookup cannot :

• Unlike VLookup, with Index Match you can match a column of data to the left of the search column (VLookup can only do to the right). In fact you can match to data anywhere in the sheet (including above and below).
• The Index Match combo tends to work with better performance than VLookup (though for most people on modern hardware and with reasonably small spreadsheets this is not really going to be an issue).

So in general I would say, stick with VLookup for most situations but remember that the Index Match combo is there just in case you need it.

Examples

Now let's look at a few examples to see what all this looks like in more real life situations and how they may be combined with other features to create useful systems.

Example 1 - Class marks overview

In this example we have a class of students. For each task we want to identify the best performing student. To achieve this we have combined the index match function with the max function.

This can be a nice way to quickly get an overview of who your best performers are.

Example 2 - Quick reports

In this example we will use a dropdown with a lookup so that we can easily modify what we are getting a report on.

For the two cells B1 and B2 we have used Data validation with a list from a range so that they both get a dropdown meny with the Departments and Months in them. This means that the user can easily change those values to see the score for that department and that month. In order for this to work we need to know which column the department is in and this is where cell C1 comes into play. This value will then be referenced in the lookup in B3 to get the right department.

Summary

Lookups are an effective means to identify particular data within a table based upon an alternative column of data. Used effectively they can save you time in identifying particular records and save you from accidentally missing an entry (especially when you have a large amount of data to sift through. They can be tricky to understand at first but are well worht the effort to learn.