Look up information in a table using VLOOKUP()

Looking up and extracting information from a table is commonplace for the majority of spreadsheet users.

Excel has a range of functions that you can use to achieve this including VLOOKUP().

Although the Excel lookup functions can seem quite straightforward, it’s very easy to get the wrong answer if you don’t fully understand how they work.

The V in the name of the function refers to Vertical, as it is used to review data in columns. To find information in rows you can use the HLOOKUP (horizontal) function which works in the same way.

This week’s post will explain the basics of VLOOKUP().

VLOOKUP()

The VLOOKUP() function assumes that your data is arranged as a table with different elements of the information in different columns. In this example we have a table of data about a music collection with columns holding information about: Track name, Album name, Year released and Artist.

Getting an Exact match

If we wanted to find the album the track ‘Whole Lotta Love’ featured on, we could use VLOOKUP() to find this information. The function looks for a value in the left most column of a table of data, and returns a value in the row where it finds a match, from one of the other columns. In this example we have typed the track title ‘Whole Lotta Love’ into cell A2 and we use VLOOKUP() to find which album it is on:

We could use the Insert Function screen to enter our function or just be guided by the on screen prompts.

The VLOOKUP() function takes up to four arguments as follows:

=VLOOKUP($A2,Music,2,FALSE)

Lookup_value

We want to find Album name from the Track name, so we have entered a track name in cell A2, We might want to copy our formula down to other rows, or across to column C, so we will leave the row reference relative but fix the column reference:

$A2

Table_array

This defines the area of the table that we will be using to find our Album name. As a minimum, we must include the column that contains our Lookup value – Track Name, and enough columns to the right to include the column that contains the value we want to use. In this case, our data has been set up as an Excel Table to which we have applied the name ‘Music’. We can include the whole of the table data (excluding the headings) by using ‘Music’. The advantage of using a Table and a Table name is that this will allow our VLOOKUP() reference to automatically adjust to include any new rows or even columns added to our Table. For a ‘normal’ block of cells the reference would just be to a range of cells.

Col_index_num

This argument defines which column of our lookup table provides the data that we want to display in our cell – in this case it is the second column, so we enter 2.

Range_lookup

This is an optional argument as shown by the square brackets. By entering FALSE or 0 we force Excel to perform an exact match. Leaving it blank or entering TRUE or 1 would perform a very specific type of approximate match.

Here is our result:

To get started on VLOOKUP() and find out more about HLOOKUP(), login to your Filtered account and select the module below:

Section 4 ‘Data Handling’ > Unit 5 ‘Lookup and Reference’ > Module 1 ‘Looking Up Information in a Table’

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.