How to VLOOKUP Multiple Criteria and Columns in Google Sheets
You will get to know what is syntax of a regular VLOOKUP function and how to VLOOKUP multiple criteria and columns in Google Sheets further in this article. Keep reading to learn more about it in detail.
How to VLOOKUP Multiple Criteria and Columns in Google Sheets What is the Syntax of a Regular VLOOKUP Function? When Do We Need to VLOOKUP Multiple Criteria in Google Sheets? How to Use VLOOKUP with Multiple Criteria in Google Sheets? Method 1: Using Helper Column Method 2: Using ARRAY FORMULA
What is the Syntax of a Regular VLOOKUP Function?
The syntax of a regular VLOOKUP function in Google Sheets is: Each of these inputs stands for,
search_key: It is the value that you are searching for in the first column of the range. It can be the cell that contains the value or a cell that can be used as a reference to the cell containing the value. range: It is a range of cells comprising cells that the VLOOKUP function looks for the values. You need to identify the column which contains the search_key as the first column. This range should also include the column which consists of the target value. index: index is the term used to refer to the column number within the range which comprises the target value. The first column of the range has an index of 1, the second column has an index of 2, and so on. is_sorted: It is the part of the syntax which denotes if the column needs to be sorted or not. It can be done by using True or False.
Now that you have understood the syntax of a regular VLOOKUP function, let’s proceed to understand when do we need to VLOOKUP multiple criteria in Google Sheets.
When Do We Need to VLOOKUP Multiple Criteria in Google Sheets?
There can be different situations and reasons why you need to VLOOKUP multiple criteria in Google Sheets. Some of the reasons are listed below:
It can be used in cases when you need to look for different tables to find scores of students in a particular subject, from a department, and in specific terms. Another situation can be when you have to get two criteria right to find a value. For example, you may require the attendance of an employee who has managed to get a performance bonus. One more instance, where you can use VLOOKUP multiple criteria in Google Sheets is when you have multiple data in a single column. For example, daily attendance of an organization consists of multiple details together like the present, absent, on medical leave, etc.
It can be used in way more situations than we have listed here. Let’s move on to the next section of the article. Also Read: How to Add Multiple Lines in One Cell in Google Sheets
How to Use VLOOKUP with Multiple Criteria in Google Sheets?
You can use two methods to use VLOOKUP with multiple criteria. The first method involves using a helper column to retrieve the required values. Read further to see how this method unfolds to get VLOOKUP multiple criteria into single column. Note: We will be using data that includes Maths and Science scores of 3 terms compiled together with the name list of students. With the below-mentioned methods, we will sort the Maths scores of all the 3 terms separately.
Method 1: Using Helper Column
Using the helper column is one of the methods that can be used to VLOOKUP multiple criteria into single column. This method involves using an additional column that can be referred to as the helper column. In this case, we will insert the helper column right before the name column. This will enable the helper column to become the first column in the range. We will use the helper column to combine the values of the Name and Term columns. Follow the steps mentioned below, Using a helper column:
Open the Google Sheet with the required data.
Insert the helper column left to the Name column by right-clicking on the column header of the same column and clicking on Insert 1 column on the left.
In the first cell of the helper column, type the formula =C2&”,”&D2 to combine the cells.
Press the Enter key to see the result of the combination, i.e. John,1.
Drag the corner of the cell to fill the column with the same formula.
Select the desired cell where you want the value and apply the VLOOKUP formula.
Enter the search_key that is the reference cell H7 and I6 by adding ampersand and separator. Lock the respective cell and column by pressing the F4 key.
Add comma (,) to move to the next syntax range. Select the columns that contain the values that we need to find the VLOOKUP value for.
Lock the values by pressing the F4 key and add a comma (,) to move to the next argument index to perform VLOOKUP multiple criteria and columns in Google Sheets.
In the index argument, type the column number which gives you the target value. In this case, it is the 4th column. So, type 4 in the index value.
Add comma (,) to move to the is_sorted argument. Type 0 to get the exact match.
Close the bracket and press Ctrl+Enter to get the required value.
Drag the corner of the cell and apply the formula to complete the table.
This method explains how to VLOOKUP multiple criteria and columns in Google Sheets using a helper column. Now we will see how to VLOOKUP with multiple criteria in Google sheets using ARRAY FORMULA. Also Read: How to Use VLOOKUP with Multiple Criteria
Method 2: Using ARRAY FORMULA
Another method is to VLOOKUP with multiple criteria in Google sheets using ARRAY FORMULA. Both methods work in the same way the only difference is that you do not need a helper column in this method to get done VLOOKUP multiple criteria into single column. Instead, you will have to build the values of the helper column and range using the formula. Read through the below-mentioned steps to grasp the process of finding values with VLOOKUP using the ARRAY FORMULA. We will use the same example that we used in the above-mentioned method.
Start by typing the = ARRAY FORMULA in the required cell.
Enter the VLOOKUP formula.
Select and lock the reference cells that are G7 and H6 by pressing the F4 key. Separate them using an ampersand and separator.
Add comma (,) and start constructing the range by opening a curly bracket.
Select the first column which is the Name column and lock the cells by pressing the F4 key.
Add a separator (&”,”&) and select the Term column which we will combine with the Name column. Lock the selected cells by pressing the F4 key.
Add comma (,) and select the third column Maths which gives the target value and complete the range.
Close the curly bracket and add a comma (,) to move to the next argument.
Type the index value of the column that will give you the target value. Here, it is we will type 2 to get the values from the Maths column.
Add comma (,) and type 0 to get the exact match followed by closing the bracket to close the VLOOKUP formula.
Close the bracket again to close the ARRAY FORMULA.
Press Enter key to get the result.
Drag the corner of the cell to get the result throughout the table.
This is how you get the required values when you have to VLOOKUP with multiple criteria in Google Sheets using Array Formula. Recommended:
How to Delete a Draft Reel on Instagram How to Add, Change and Delete Telegram Profile Picture 6 Ways to Remove Duplicates In Google Sheets How to Quickly Wrap Text in Google Sheets?
We hope that this guide on how to VLOOKUP multiple criteria and columns in Google Sheets was helpful. You can drop your queries and topic suggestions for future articles in the comments section below.
