VLOOKUP Formula in Google Sheets
The VLOOKUP function is one of the most useful features that Google Sheets provides.
It’s also the most popular one when it comes to searching for information in a huge data set.
What is the VLOOKUP Formula?
The V in VLOOKUP stands for vertical, so it performs a vertical lookup.
It looks up data vertically in the first column of the input range, based on an associated key-value, and it returns a value in the same row from another column.
The VLOOKUP function has the following syntax:
=VLOOKUP(search_key, range, index, [is_sorted])
When to use VLOOKUP for marketing stuff?
- Combining different datasets for audits.
- Pulling keyword data from different sources.
- Cross-referencing ANY data with other metrics.
How To Use VLOOKUP in Google Sheets
The arguments of the VLOOKUP function are:
- search_key is the key-value to search for. For example, you can search for the number 42, the word “Cats”, or the value in cell B4. The VLOOKUP searches are case-insensitive, meaning that it doesn’t distinguish upper-case and lower-case letters.
- range must consist of two or more columns of data for the vertical search. The function always searches for the key in the first (leftmost) column of this range.
- index is the column number in the range from which a matching value will be returned. The first column in the range is numbered 1, and the index must be between 1 and the number of columns the range has.
- is_sorted (optional) indicates whether the VLOOKUP function should return the nearest match (when it’s TRUE) or exact match (when it’s FALSE). It’s TRUE by default, but it’s should be set to FALSE when your range is not in sorted order.
If is_sorted is FALSE…
The function returns an exact match.
If the first column of the range contains 2 or more values that exactly match search_key, the fist value found is returned.
If there is no exact match, you get an error. In most cases, this is the desired behaviour.
If is_sorted is TRUE (which is its default value)…
The function returns an approximate match.
In this case, the first column of the range must be sorted in ascending order (smallest to largest or alphabetically).
The formula searches for the exact match first and if it’s not found, it starts searching for the closest match that is less than or equal to the search_key.
If every value in the search column is greater than the search key, an #N/A error is returned.
VLOOKUP Formula Examples
Let’s see a simple example. Using the VLOOKUP function, search for an order ID and return the name of the product it belongs to.
The following formula will do this in this example:
=VLOOKUP(E2, A2:C12, 2, FALSE)
Here’s what it does:
- The function looks for the search term that is in cell E2 (search_key = E2).
- It will look up in the first column of the range A2:C12 (range = A2:C12) which is the Order ID.
- Once it finds the search key in this column, it returns the value of the second column of this row (index = 2).
- It only searches for exact matches, so approximate results won’t be returned (is_sorted = FALSE).
This is how VLOOKUP works with unsorted ranges. Now let’s take an example with a sorted lookup range. Suppose you want to see how much traffic a specific social media post got.
With sorted ranges, you can search for approximate matches which works great with numbers.
The function to do this looks like this:
=VLOOKUP(E2,A2:C12,3,TRUE)
In this case, the VLOOKUP function looks for the number 5000 in the Visitors column (because it is the first column of the range) and returns the value of the third column (because of index = 3), so the platform of that post.
The fourth argument, is_sorted is TRUE here meaning that the function searches for approximate matches in case there is no exact match.
Since the exact number 5000 is not found in the list, the function returns the closest value that is lower than this number.
If you look at the table, you can see that it is a Pinterest post with 4789 visitors.
Normally, the VLOOKUP function only allows you to return the value of one column (which you can set with the index variable). But there is a solution if you would like to include more than one columns in your results.
Let’s look at the first sheet again. Say you want to search for an Order ID and you want to retrieve all the data that is associated with this ID.
You can add a wrapping ARRAYFORMULA function to your VLOOKUP function to do this.
And since the ARRAYFORMULA function needs array references, change the single column references to an array reference in the third argument.
So instead of putting a single number as the index, write an array that contains all the column number you want to include in the following way: {2, 3, 4}.
The whole VLOOKUP function with a wrapping ARRAYFORMULA will look like this:
=ARRAYFORMULA(VLOOKUP(F2, A2:D12, {2,3,4}, FALSE))
So far you have seen how to output the results of a VLOOKUP function.
However, it’s possible that you don’t just want to output it, but also calculate with the results.
You can use the returned value(s) of a VLOOKUP function as the arguments of other functions.
For example, you can search for a keyword in a sheet that contains the number of sales for the keywords in different locations.
You can look up a keyword and output the sales from more than one column with the above solution using an ARRAYFORMULA.
=ARRAYFORMULA(VLOOKUP(G2,A2:E12,{2,3,4,5},FALSE))
After that, say you want to sum these numbers that were found in multiple columns. To do this, you can nest the result of the VLOOKUP function within another function such as SUM.
The whole function is the same as before with an extra SUM function layer:
=SUM(ARRAYFORMULA(VLOOKUP(G2,A2:E12,{2,3,4,5},FALSE)))
Summary
That’s it!
As you can see, the VLOOKUP function has many possibilities.
It can automate your work a lot when dealing with a huge amount of data and provides a helpful tool to search for complex information.