## What Is **VLOOKUP Two Criteria In Google Sheets**?

The

VLOOKUPtwo criteria in Google Sheets is aVLOOKUPformula 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

VLOOKUPtwo 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)

**1. How to perform efficient inventory management using VLOOKUP with two criteria in Google Sheets?**

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**.

**2. When do we need to VLOOKUP two criteria in Google Sheets?**

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.

**3. What are the common issues in VLOOKUP with two criteria in Google Sheets?**

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