What Is VLOOKUP Two Criteria In Google Sheets?
The VLOOKUP two criteria in Google Sheets is a VLOOKUP formula that returns a value based on the two specified conditions. In other words, the formula requires checking two search key values in a lookup range to obtain the targeted return value.
Users can utilize the VLOOKUP two criteria formulain Google Sheets when reviewing sales data for determining the top-selling item in a specific zone, with the item name and zone being the two search conditions..
For example, the following dataset lists employees’ first and last names, and their contact numbers.
The requirement is to update the employee’s contact number in cell F3, whose first and last names are cited in cells F1 and F2.
Then, considering the definition of VLOOKUP two criteria in Google Sheets explained earlier, we can apply the VLOOKUP() with two conditions in the target cell. We shall achieve the required data in the specific cell.
However, typically, the VLOOKUP(), like the Excel VLOOKUP function, works with only one condition. So, for the function to work with two criteria, we shall insert a helper column in the source dataset to combine the two conditions and secure unique search key values.
In this case, we insert a column before the first column of the source dataset and set the column heading as Helper Column. Next, we use a formula to append an employee’s first and last name with a space character in between (Refer to the cell A2 formula in the image above). We shall apply the formula in column A cells A3:A6.
Next, we shall enter the VLOOKUP() in the new target cell G3. If we observe the VLOOKUP two criteria in Google Sheets arguments, the first argument, search_key, is an expression. It concatenates the employee’s first and last names with a space character in between, mentioned in cells G1:G2. So, now the search key value is in the same format as those of the values in the helper column. Next, the second argument, range, is A2:D6, which is the source dataset range with the newly added helper column. The third argument, index, is 4, indicating that the function must fetch the return value from the fourth column, column D, in the dataset (which includes the helper column). Finally, since we require the function to find an exact match for the search key, the last argument is_sorted is 0.
Thus, for the furnished VLOOKUP two criteria in Google Sheets arguments, the function looks for an exact match for the unique search key Peter Murphy in the first column of the lookup range A2:D6. It locates the match in cell A6. So, the formula VLOOKUP two criteria in Google Sheets returns the value in the cell where row 6 and column D meet, which is the cell D6 value of 1-732-269-2479.
Table of contents
Key Takeaways
- The VLOOKUP two criteria in Google Sheets is a VLOOKUP(). It needs to look for two search keys in the lookup range to return the corresponding return value.
- We can apply the VLOOKUP function with two conditions in Google Sheets using two methods. The first method involves a helper column containing unique search key values created by combining the two conditions. On the other hand, the second method is to implement the VLOOKUP functionwithin the ARRAYFORMULA function.
- Users can use the VLOOKUP function with two criteria in Google Sheets for broadening their sales data analysis and enhancing inventory management.
What Do Two Criteria Mean?
Typically, the VLOOKUP() works with only one condition. However, as mentioned in the definition of VLOOKUP two criteria in Google Sheets explained earlier, we can use the VLOOKUP() with two conditions. So, the function involves two search keys that represent the two conditions. Next, it looks for a match for the two search keys in the lookup range and returns the corresponding return value as the output.
When we integrate two criteria into a VLOOKUP function, they improve the function’s versatility and considerably enhance its power in data management and manipulation.
Using A Helper Column To VLOOKUP Two Criteria In Google Sheets
The source dataset contains a list of sales representatives, the zones they belong to, and the sales they generate.
We must determine the sales generated by the representative in the zone, which are specified in cells F1 and F2. We shall consider cell F3 as the target cell.
We can secure the required output using VLOOKUP two criteria in Google Sheets with a helper column.
Step 1: Insert a column before the first column of the source dataset. Name the new column as Helper Column.
Step 2: Select cell A2 in the helper column, enter the formula shown below, and press Enter.
=B2&”_”&C2
The formula concatenates the sales representative name, an underscore symbol, and the zone, cited in row 2.
Next, use the fill handle option to update the formula in the rest of the helper column cells A3:A9.
Thus, we secure the required unique search key values, required while using VLOOKUP two criteria in Google Sheets.
Step 3: Select the new target cell G3, enter the VLOOKUP() with two conditions, and press Enter.
=VLOOKUP(G1&”_”&G2,A2:D9,4,0)
The first argument in the VLOOKUP() is an expression that concatenates the sales representative’s name and their zone, with an underscore in between. The search key format is the same as the unique values in the helper column.
Next, the second argument is the search range A2:D9, which includes the helper column range and the source dataset range.
Next, the third argument value of 4 indicates the fourth column of the search range, column D, containing the potential return values. Finally, the fourth argument value is 0, indicating that the function must find an exact match for the search key value in the search range.
So, the search key value in the VLOOKUP() is George_North. The function then looks for the search key value of George_North in the first column of the lookup range of A2:D9. It finds a match in cell A5. So, the formula VLOOKUP two criteria in Google Sheets returns the value in the cell where row 5 and column D meet, which is the cell D5 value of $9,000.
Using The ARRRAYFORMULA Function To VLOOKUP Two Criteria In Google Sheets
The first dataset lists teams, the floors they are on and their car parking slot allotment statuses.
The second dataset shows the employees, their teams and the floors where they have their workstations.
The aim is to update the car parking slot allotment status for each employee based on the team they belong to and the floor where they have their workstation. We shall use the column H cells as the target cells.
Step 1: Select the target cell H2, enter the ARRAYFORMULA-based VLOOKUP() for two criteria, and press Enter.
=ARRAYFORMULA(VLOOKUP(F2&””&G2,{$A$2:$A$10&” “&$B$2:$B$10,$C$2:$C$10},2,false))
Step 2: With the cell H2 chosen, drag the fill handle towards the last target cell, H11, to apply the formula in the remaining target cells.
First, the ARRAYFORMULA() helps build a kind of virtual table with the following columns:
- A column holding values concatenated using values in cells $A$2:$A$10 and $B$2:$B$10, with a space character between each cell value ($A$2:$A$10&” ” &$B$2:$B$10).
- A column holding the values from the Car Parking Slot Allotment Status column of the first dataset ($C$2:$C$10).
Please note that the ranges are provided within curly braces since we require the function to return a virtual range of cells.
Next, we enter the VLOOKUP() with the following argument values:
The search key value is a concatenation of the Team and Floor Number values we aim to look up, with a space character in between. The lookup range is the virtual range the ARRAYFORMULA() returns. Next, as the virtual range contains two columns, where the Car Parking Slot Allotment Status column (holding the return values) is the second column, we supply the third argument value as 2. Finally, since we require the VLOOKUP() to find an exact match for the search key, the last argument value is 0.
The supplied search_key argument value and the values in the first column of the virtual range are in the same format. So, the VLOOKUP() looks up the corresponding Car Parking Slot Allotment Status to return it as the output.
Important Things To Note
- Assume the VLOOKUP two criteria in Google Sheets does not find a match for the search key value. Then, the function returns the #N/A! error value.
- Ensure the format used to enter the unique search key values in the helper column and the search key value in the VLOOKUP() is the same. Otherwise, the function output will be the #N/A! error value.
- Ensure all the ranges specified in the ARRAYFORMULA-based VLOOKUP() with two criteria are of the same size. Otherwise, the formula output will be #REF! error or an incorrect value.
Frequently Asked Questions (FAQs)
We can perform efficient inventory management using VLOOKUP with two criteria in Google Sheets, as explained below with an example.
Consider that we have a dataset containing a list of stationery items, their categories, and the inventory levels.
We must update the inventory level of the item cited in cell G1 while also considering its category specified in cell G2. Assume cell G3 is the target cell.
Then, we can use the VLOOKUP() with two conditions in the target cell and secure the required output. But first, we shall update the required unique search keys in the helper column.
Step 1: Choose cell A2, enter the following formula, and press Enter.
=B2&” – Cat “&C2
The formula appends the stationery item and category with the specified text between them.
Next, use the fill handle to implement the formula in the remaining helper column cells.
Step 2: Choose the target cell G3, enter the VLOOKUP(), and press Enter.
=VLOOKUP(G1&” – Cat “&G2,A2:D9,4,0)
The first argument in the VLOOKUP() is an expression that combines the stationery item and its category with the specified text between them. The search key format is the same as the unique values updated in the helper column.
Next, the second argument is the search range A2:D9, which contains the helper column range and the dataset range.
Next, the third argument value of 4 denotes the fourth column of the search range, column D, from where the function must get the return value. Finally, the fourth argument value is 0, denoting that the function must find an exact match for the cited search key value in the lookup range.
So, the search key value in the VLOOKUP() is A3 Sheets Bundle – Cat B. The function then searches for the search key value of A3 Sheets Bundle – Cat B in the first column of the search range of A2:D9. It finds a match in cell A8. So, the VLOOKUP() returns the value in the cell where row 8 and column D meet, which is the cell D8 value of 20.
We need to VLOOKUP two criteria in Google Sheets in the following scenarios:
• We have individual columns holding first and last names in the source dataset. So, we would be required to search both columns to get a corresponding return value.
• We may be required to check for two conditions to obtain the target return value. For instance, we need the sales figures of only those employees who are senior engineers and belong to a specific team.
The common issues in VLOOKUP with two criteria in Google Sheets are as follows:
• The VLOOKUP() does not find a match for the search key value.
• The formats used to update the unique search key values in the helper column and the search key value in the VLOOKUP() are different.
• The ranges specified in the ARRAYFORMULA-based VLOOKUP() with two criteria are of different sizes.
Download Template
This article must be helpful to understand the VLOOKUP Two Criteria In Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is VLOOKUP Two Criteria In Google Sheets. We explain how to use VLOOKUP two criteria formula in Google Sheets with examples. You can learn more from the following articles. –
Leave a Reply