Here’s how to look up values in tables in Google Sheets using the really useful VLOOKUP function (Vertical Look up). You give it something to look for in the table and it gets the value you’re after from a different column in that row. As always the best way to show what it does is through examples, showing different aspects and uses of the VLOOKUP function.
Example 1 – Look up a book number and get the name of who has it
Here we have a list of books, which have an individual reference number, a title and the teacher who is using it.Here we want to find out who has book number 0185. With a small list like this we can easily see who has which book, but imagine a list with hundreds or even thousands of books, that would require looking through from list manually. Of course, this is where the VLOOKUP function comes in, as this will deliver the information you need instantly.
We need two things, firstly, a cell to type in the book number we want to find, e.g. C14. Then secondly, a cell to deliver the result, in this case, C15.
In cell C15, we add the VLOOKUP formula. As we can see from the help information, we can see that it requires 4 bits of information.
search key: What we’re looking for
range: where we should look for the information, often this is the whole table
index: which column is the information we’re looking for in. The first column of the range, is index 1, the second is index 2, and so on.
is sorted: Is the information we’re looking up, i.e. the search info, sorted in order or not? We either put TRUE or FALSE. Another way to look at it is whether you want to find a specific piece of information, i.e. an exact match, or whether it falls within a range (which we’re look at in example 3).
So, in this example, we’re looking at the contents of cell C14 (book number), looking in the table (range A2:C14), then looking up the value in the third column (teacher in column C), and finally I’ve stated the range is not sorted as I want an exact match to the book number, i.e. FALSE.
=VLOOKUP(C14,A2:C12,3,FALSE)
As you can see it’s correctly found that John has book number 0185.
Example 2 – Display a message instead of an error, if something cannot be found
If I tried looking for a book that’s not on the list, e.g. book number 0191, it will report an error “#N/A”.
If we hover over the error, it will tell us that it couldn’t find that book number.
The error looks a little unsightly, and we can modify the VLOOKUP function up so that if it does find an error that it displays a message instead.
To do this, we wrap the VLOOKUP function up in an IFERROR function. Before the VLOOKUP function we type IFERROR then open the brackets. Here it needs two parts, the value (here the VLOOKUP formula), and what to display if it there’s an error.
After the first part we add a comma and then for example, write a message we want to display if it finds an error.
In this case, let’s display “Not found”, if it doesn’t find the book number. Then we close the brackets, so it’s all wrapped up.
=iferror(VLOOKUP(C17,A2:C13,3,FALSE),“Not found”)
As we can see the book number in cell C17, hasn’t been found and so the message “Not found” appears in C18.
Example 3 – Finding values within ranges
We can also look for values that fall within different ranges. Here we different marks in a level test that correspond to different levels. So, for example, someone who gets 60 in the test, has an advanced level.
So, here we can set it up to look for the level that corresponds to the test mark in cell B9. So in the formula, we add the B9 as the search key, the range where the table is (A2:C7), column 3 (level) and this time we state it’s TRUE as the value we’re looking for is within one of the ranges and not an exact value, so we need to tell Sheets that the ranges are sorted. If we put FALSE, we’ll get an error as “20” isn’t specifically stated in columns A or B.
=vlookup(B9,A2:C7,3,TRUE)
Sure enough, it finds that 20 is in between 16 and 25 and therefore the level that corresponds to that is Intermediate.
Sometimes it’s more convenient to store the reference table on a different sheet. For example, we use a Google Form connected to a Google Sheet to record level tests completed and the results of the tests appear on one sheet, then using VLOOKUP, the results are compared with the reference table on another sheet.
To do that we modify the formula, so that the sheet name is added to the range. E.g. the table is on the sheet called “Ref” in cells A2:C7, so we write Ref!A2:C7.
=vlookup(B12,Ref!A2:C7,3,TRUE)
Example 4 – Including formulas in a VLOOKUP function
In the example above, we had already calculated the total mark the student got in a test, then used that total to find their level. We can also do the calculation directly within the VLOOKUP formula.
Here there are four parts of the test, Reading, Listening, Speaking, and Writing. What we want to do is sum those parts then look up the level that corresponds to that total.
In the search key we add a SUM function, i.e. sum(F2:I2), which adds up the four parts of the test, that we complete the VLOOKUP formula as before.
=vlookup(sum(F2:I2),Ref!A2:C7,3,TRUE)
And again it finds the correct level.
Example 5 – Using wild cards in VLOOKUP
Sometimes we don’t want to state the complete search term we’re looking for. For example, here we want to look up a class for a teacher and either we don’t want to type in their full name, or maybe we don’t know their surname.
Here we have a list of teachers and the classes they have.
We want to find out which class Betty has. Using a normal VLOOKUP formula won’t find her as her full name in used in the table.This is where wildcards can be used.
In the search key refer to the cell and add the wild card symbol “*”. This then looks for anything beginning with the name in B7, i.e. the first person it can find with the name Betty. We join the contents of B7 with the wild card by typing B7&”*”.
The rest of the formula is as normal.
=VLOOKUP(B7&“*”,A2:C5,2,FALSE)
Note, if there were two Bettys in the list, it would only find the first one.
There are two wild cards we can use. “*” will ignore any number of characters, and “?” will ignore a single character. So, in this case B7&”?” wouldn’t find Betty Rubble.
Example 6 – Looking up multiple values in a table
So far we’ve been searching for one thing, but what happens if we want to search for more than one? Well, that’s possible too. In this example, we want to find the quantity of a particular product by a particular company, i.e. we’re looking for two values then finding the quantity that corresponds to those.
We have two companies (Widgets and Gizmos) and they both sell two products (VR Headsets and Virtual Assistants). We want to find how many VR Headsets Gizmos has.
To allow us to look for two values, one of the easiest ways to do this is to create an additional column where we combine the information in the first two columns. So here we’ve added a ‘helper’ column in column C, where we join the company and product together. In effect we’re creating one value which we can look up later on.
In column C, we type the following formula:
This gets the value in cell A2 adds a space then adds the value in cell B2. We could also use CONCATENATE. We then copy that formula down column C, so we end up with all the companies and products.
In cell B9, we add the VLOOKUP function. As we are going to allow the user to type in the company in B7 and the product in B8, we need to get those two values and join together, then look for that combined value in the table.
So, our search key is B7 and B8 joined together with a space in the middle, similar to the formula used in the helper column.
Then we add the range, note I’ve just put columns C and D instead of the whole table. It doesn’t make any difference, just remember that when it comes to the index, as I’ve only selected two columns, the information I’m looking for is in the second column, i.e. index 2.
=vlookup(B7&” “&B8,C2:D5,2, FALSE)
Example 7 – Retrieving multiple values from a table
In a way this example is the opposite to example 6, in that this time we have one value, a name, and we want to retrieve more than one value from the table.
Here we have some students and their test results across the year. Every semester I need to complete a report and I only want semester 1’s results in one report and then semester 2’s in the other. I could refer to the individual cells, for example, in the semester 1 report for Ringo, I could write in cell B10, =B5, to get his test 1 result, but of course having to do that for all his tests and then for all the other students is a slow way to do it. Instead we can use a VLOOKUP formula with an array.
An array in this case, is just telling Sheets how many spaces to the right you want to go to get the value. In the table above, our table goes from column A to G, or if they were numbered it would go from column 1 to 7. So to get the test results for Ringo for semester 1, we want the cells in columns B, C, and D, or in terms of numbers, 2, 3 and 4.
To do this, we need use the ARRAYFORMULA function along with the VLOOKUP one. All it’s doing is allowing us to retrieve more than one value.
In cell B10, we start off with =ARRAYFORMULA then the VLOOKUP function. We refer to A10 (student’s name), A3:G6 (the table), then within CURLY brackets we add the column numbers we want to retrieve. As stated before, that’s columns 2, 3, and 4.
=ArrayFormula(vlookup(A10,A3:G6,{2,3,4},FALSE))
As we can see it’s retrieved the 3 test results from the table. Note, there’s no need to type anything in cells C10 or D10, they are filled in automatically.
To get Ringo’s second semester tests, we use the same formula, except this time we looking in columns 5, 6, and 7 (i.e. columns E, F, and G).
=ArrayFormula(vlookup(A14,A3:G6,{5,6,7},FALSE))
Example 8 – Retrieving multiple values from a table (part 2)
The multiple values we extract from a table don’t have to be next to each other as in the example above. We can get them from anywhere along that row.
In this example, the students have done 3 tests and each test has a written component and an oral test. I want to separate their written marks from their oral ones and put them in the tables below.
This is just like the example above. We get the student’s name from cell I10, look in the table I3:O6, then get the values from columns 2, 4, and 6 (columns J, L, and N).
=ArrayFormula(vlookup(I10,I3:O6,{2,4,6},FALSE))
Similarly, we can get his oral marks, but this time we get the values from columns 3, 5, and 7 (columns K, M, and O).
=ArrayFormula(vlookup(I14,I3:O6,{3,5,7},FALSE))
Example 9 – Using Named Ranges with VLOOKUP
Sometimes it’s useful to name the range in your table, so you know which table you’re referring to, particularly if you have more than one in your document. Also, if for some reason there’s a chance that the formula will be moved or copied elsewhere, then a Named Range is absolute, i.e. it always refers to the same range even if it’s moved.
Looking back at our first example, let’s create a Named Range for it.
First we select the whole table.
Then right-click and select “Define named range…”.
This will open the sidebar where you can name the range and edit it if necessary.
Change the default name to something meaningful, for example “BookList”. Note, you can’t have a name with spaces in it. Then click Done.
Now when we set up the VLOOKUP formula, in the range we just type the name of the range, we will see the range get highlighted.
The rest is as before and we can see that it works just as first example.
=vlookup(C14,BookList,3,FALSE)
Say we want to move the book number entry and teacher to a different part of the sheet, this doesn’t affect it at all, as it continues to refer to that particular range.
We could do the same by adding dollar signs to the range, to make it an absolute reference and not a relative one.
A couple of final points. The search keys used are case-insensitive, so it doesn’t matter if you type betty or Betty, it will find the same result. Here we’ve looked at Vertical Lookup, but there is also Horizontal Lookup, HLOOKUP, which works in a similar way, and will depend on how your data is set up. However, VLOOKUP is far more common.
This post is taken from my book “Google Sheets Functions – A step-by-step Guide“, available on Amazon here.
a
3 comments
Comments are closed.