If you’re using Google Sheets and need to find information from multiple sheets or tables, it can be difficult and time-consuming to do it manually. Luckily, Google Sheets has a useful feature called VLOOKUP, or vertical lookup, that can help you with this problem.
With VLOOKUP, you can automatically search for a value and retrieve data from another table, whether it’s on the same sheet or a different one.
However, VLOOKUP has one limitation: it only allows you to search for one column at a time. But don’t worry, in this guide, we will show you how to VLOOKUP with multiple criteria/values in Google Sheets.
But instead of just using the VLOOKUP function, which won’t give us exactly what we want, what we will do is leverage the VLOOKUP function along with other powerful Google Sheets functions, like the ArrayFormula function, to perform VLOOKUP with multiple criteria/values in Google Sheets.
Possible Use Case Scenario: VLOOKUP With Multiple Criteria/Values in Google Sheets
Imagine you are a sales manager at a company, and you have multiple sales sheets containing information about different products, regions, and salespeople. You need to analyze the sales data to find specific information, such as the total sales made by a particular salesperson in a specific region for a specific product. Manually searching through each sheet to find this information would be time-consuming and prone to errors.
In this scenario, you can use the VLOOKUP function in Google Sheets to simplify the process. By combining VLOOKUP with other functions like Importrange, you can quickly search for the desired information based on multiple criteria or values.
This allows you to retrieve the necessary data from different sheets or tables in a seamless and efficient manner. In the following sections, we will guide you through the steps to achieve this using Google Sheets’ features.
But before we get into the nitty-gritty of how to VLOOKUP with multiple criteria/values in Google Sheets, let’s quickly go over the basics together- understanding the dynamics of the VLOOKUP syntax.
VLOOKUP Syntax as a glance
If you’re just hearing about the VLOOKUP syntax, here is exactly what it looks like:
=VLOOKUP(search_key, range, index, [is_sorted])
For better insights, let’s quickly break down the different parameters that make up the VLOOKUP syntax:
- search_key: This is the value you want to find in another table. It can be a specific value or a reference to a cell that contains the value you’re searching for.
- range: This refers to the range of cells in the source table where you want to search for the search_key. Make sure this range includes the column with the search_key as its first column and the column that contains the target value you want to retrieve.
- index: This is the column number within the range that holds the target value you want to retrieve. Remember that the first column in the range is assigned an index of 1, the second column has an index of 2, and so on.
- is_sorted: This is an optional parameter that can be set to either TRUE or FALSE. It indicates whether the search column needs to be sorted or not before performing the lookup.
By understanding and applying these simplified concepts, you’ll be able to use the VLOOKUP function in your data analysis tasks effectively.
Let’s quickly look at a basic example that involves using the VLOOKUP function in Google Sheets.
We will use the following sample data for our example.
If you look at the image below, you’ll notice we have two tables. One table contains client information, and the second contains subscription information.
As both tables share a common column, namely the Client ID, it can be regarded as a key value or a unique identifier in both tables.
Assuming we want to retrieve the amount due for client ID “WS1091,” we need to retrieve that information from the Client’s information table using the VLOOKUP function. To do that, you’ll need to enter the following VLOOKUP formula in cell F3.
=VLOOKUP(A3,$A$3:$C$8,3,false)
If you did everything right, the result should be generated in cell F3. Here is what ours looks like.
Understanding the Situations Requiring VLOOKUP with Multiple Criteria in Google Sheets
For the example we just showed you, we only needed to look for a single criterion: the Client ID. However, in many situations, the criteria can be more complex than that.
Here are some examples of why you might need to use VLOOKUP with multiple criteria in Google Sheets:
- If the source table has separate columns for first and last names, you must search for both columns to find the corresponding value.
Similarly, you might have different categories or attributes for the data, such as product type and region, and you need to search for values based on multiple categories simultaneously.
- Sometimes, you might need to match multiple conditions, such as finding values that meet a certain date range and a specific product category.
- To retrieve a value, sometimes you have to check if two or more conditions are met. For instance, let’s say you want to find the total scores of students who both passed a test and studied French.
- Sometimes, you might need to search for a specific table to find out how much an employee earns based on their department and ID.
Even though there are various other scenarios similar to the ones mentioned above. However, it’s important to understand the following points:
While leveraging the FILTER function might seem more convenient for the situations discussed earlier, unfortunately, the FILTER function cannot retrieve data from a separate sheet.
Similarly, opting for IF functions can quickly become overwhelming, as it may require dealing with multiple nested IFs. Let’s face it; nobody wants to deal with that complexity!
So, it’s essential to keep these considerations in mind when tackling such situations.
How to Search with Multiple Criteria in Google Sheets using VLOOKUP
Now that you know when to use VLOOKUP with multiple criteria/values in Google Sheets, it’s time to review some practical examples to broaden your understanding of the entire concept.
For our first example, we will use the following sample data.
In Table 2, we want to find the Points for a specific Department in a particular ID. We’ll display this retrieved value in the Points column (column E).
To achieve this, there are two methods we can use:
- Using a Helper Column: We’ll create an additional column to perform the lookup
- Using the ARRAYFORMULA function: We can use this function to directly populate the Points column with the corresponding Points values without needing a helper column.
These methods will help us fetch the right points based on the Department and ID in Table 2.
Let’s start with the first option.
How to use Helper to VLOOKUP with multiple criteria/values in Google Sheets
The first method we will show you has to do with adding an additional Helper column in Table 1. This column will combine information from the cells in the criteria.
For this example, we will insert the Helper column right before the department column. So essentially, the Helper column will be the first column of the search range.
Let’s quickly add that the helper column will combine the departments and IDs of each row, separated by a space.
To simplify things, we have put together a simple step-by-step process to create and use the helper column for this example.
Let’s jump right in, shall we?
Step 1: Insert Helper Menu
As we mentioned earlier, the first thing we would like to do is insert the Helper menu in Table 1. To do this, you’ll need to right-click on the first column’s header (Column A) and choose the option to “Insert 1 column left” from the context menu.
With the new column added to your spreadsheet, rename it as “Helper column.” Here is what ours looks like:
Step 2: Type in the following formula
Now, select the first cell in the Helper column (cell A4) and enter the following formula: =B4&” “&C4
Press the enter button on your keyboard after entering your formula, as shown in the above image. Google Sheets will automatically merge the contents of cell B4 and C4 in cell A4, separated by only one space.
Here is what our sheet looks like after executing the above steps.
Step 3: Auto-fill the other cells under the Helper column
Looking at the image above, you’ll notice we only generated the result for cell F4. Now, we need to repeat the same process for the other cells. But instead of doing it manually, which will waste our time, we can simply use the Google Sheets auto-fill option to simplify our job.
The video below shows you exactly how to do that:
After executing the auto-fill option, your Table 1 should look something like this:
Step 4: Shift cells left
Now that we have generated the result for our Helper column, we need to proceed to use the VLOOKUP function. If you look at Table 2 closely, you’ll notice that the content of this table shifted one cell to the right after adding the Helper column to Table 1. If you want, you can select the content of those cells and move them one cell to the left.
The video below will show you how to do that:
Step 5: Type in the VLOOKUP formula
Now, here is where things get interesting. Head over to cell E17, which is where we want to generate our result and type in the following formula:
=VLOOKUP(B17&” “&C17,$A$4:$D$12,4,False)
After typing in the VLOOKUP formula, go ahead and press the Enter button on your keyboard. Google Sheets will automatically generate the result in our selected cell (cell E17).
Here, take a look at what Table 2 now looks like.
Looking closely at the image above, you’ll notice we only generated the result for cell E17. Now, let’s generate the result for the other cells.
But we don’t have to do it manually this time. We will simply use the Google Sheets’ autofill option to simplify our job.
The video below will show you how to do that:
After executing the steps exactly as we showed you and following the video illustration above, you’ll notice that all the points value corresponding with each department and ID have been generated in Table 2. Which is what we were after.
From our example, you can now see that performing VLOOKUP with multiple criteria/values in Google Sheets isn’t as complex as you imagined it to be.
Note: When you’re typing the VLOOKUP formula, it’s important you remember to lock the references in the second parameter by pressing the F4 key.
When you lock the references, you’ll notice that your formula looks like this:
=VLOOKUP(B17&” “&C17,$A$4:$D$12,4,False)
Instead of like this:
=VLOOKUP(B17&” “&C17,A4:D12,4,False)
We did that to prevent the search range from changing when we copy the formula to other cells.
Breaking down the VLOOKUP formula we used
While we put together today’s guide to show you how to VLOOKUP with multiple criteria/values in Google Sheets, you must understand what’s been done, especially from the context of the VLOOKUP formula we used for every example.
So let’s break down the formula below so you have a solid understanding of each parameter we used;
=VLOOKUP(B17&” “&C17,$A$4:$D$12,4,False)
- search_key: The search_key we used for our first example combines Department and ID values, which is what we want to look for. Furthermore, we separated both values using a space.
- Range: If you’ve used the VLOOKUP function in the past, you’ll know that the range should always have the lookup column as its first column. For our example, the search range starts from A4 to D12. And the reason why this is so is because A4 is the first cell of our Helper column.
- index: Since we added a new column to the left, our target column has shifted one cell to the right. To the end, the points column is now at index 4 of our search range.
- is_sorted: Adding the FALSE value for this parameter means that the first column of the search range doesn’t have to be sorted in ascending order.
Note: Considering that the search_key and the Helper column are in the same format (i.e., Department, followed by a space, then our ID), it’s easy for the VLOOKUP function to look up the corresponding points and return them.
How to use the ArrayFormula function to VLOOKUP with multiple criteria/values in Google Sheets
This method is very similar to the first method we showed you, albeit with a few differences. What’s unique about this method is that instead of having to physically create a helper column as we did in the first method, deploying the ArrayFormula function creates a dynamic helper column you can’t physically see.
The ArrayFormula method for executing VLOOKUP with multiple criteria/values in Google Sheets we are about to show you; uses the Array function to create a virtual table for the following columns.
- A unique column that houses a combo of cells in the criteria
- A target column from the search range.
Now that we have established some context, let’s show you how to apply the ArrayFormula function to VLOOKUP with multiple criteria/values in Google Sheets.
We will use the same sample data we used for our first example.
Step 1: Choose the cell where you want to generate the result
Since we want our result generated in Table 2, particularly in the column for points, we will go ahead and select cell E17.
Step 2: Type in the ArrayFormula
With the cell where we want our result generated selected, head over to the formula bar and type in the following formula
=ARRAYFORMULA(VLOOKUP(B17&” “&C17,{$A$4:$A$12&” “&$B$4:$B$12,$C$4:$C$12},2,false))
Step 3: Hit Enter
After typing your formula, the last thing we want to do is press Enter on our keyboard. Google Sheets should automatically generate the result in cell E17.
Here, take a look at what ours looks like:
From the image above, you can see that we now have our result generated in cell E17. As we did for the first method, you want to generate the result for other cells.
To simplify your job, use the auto-fill option we showed you in the first method.
With the result for the other cells generated, you should have something like this:
Final Thoughts
Using the VLOOKUP function to search for multiple criteria or values in Google Sheets can be challenging, especially for beginners. However, once you understand how it works, you’ll realize how it simplifies your work.
In this guide, we have demonstrated how to perform VLOOKUP with multiple criteria/values in Google Sheets. We began by explaining the situations where using VLOOKUP with multiple criteria/values in Google Sheets is necessary. Then, we provided you with a few examples to illustrate the concept.
By following the instructions in this guide, you can confidently use VLOOKUP with multiple criteria/values in Google Sheets without any difficulty.