VLOOKUP with Multiple Criteria in Google Sheets

What is VLOOKUP with Multiple Criteria in Google Sheets?

When you are looking for information from Google sheets, it is very time-consuming to do it manually. Hence, we have a function called VLOOKUP, or vertical look-up, to help automatically search for a value and retrieve data from another table. It could be on the same sheet or a different one. However, the VLOOKUP comes with a limitation that you can search only one column at a time. In such cases, we use VLOOKUP with multiple criteria in Google Sheets. 

In the example below, we have some fruits and their colors. To look up a pink-colored fruit, we use the following Google Sheets VLOOKUP formula in cell E2. The function looks up the search value in the range specified and gives the corresponding result from the column whose index is mentioned(2). FALSE indicates an exact match.

=VLOOKUP(D2,A1:B6,2,FALSE)

VLOOKUP with multiple criteria in Google sheets 1

In this article, instead of just using the VLOOKUP function, we will use it with other powerful Google Sheets functions for VLOOKUP with multiple criteria in Google Sheets.

Key Takeaways
  1. The VLOOKUP with Multiple Criteria in Google Sheets looks up a value in the leftmost column of a table and returns a corresponding value from the same row in another column. When we can’t directly locate such a value, we use multiple conditions till we get the result. Here, VLOOKUP stands for vertical lookup.
  2. The Syntax for the VLOOKUP in Google Sheets is as follows:

=VLOOKUP(search_key, range, index, [is_sorted]).

  • Here the argument is_sorted can be FALSE (an exact match), or TRUE, an approximate match.
  • You can use VLOOKUP with FALSE to find the result only if there is an identical value in the search column.
  • We use Google Sheets VLOOKUP multiple values if the dataset is more complex.

Syntax

The syntax of the VLOOKUP function is as follows:

VLOOKUP(lookup_value, table_array, col_indexnum, [range_lookup])

Where:

  1. Lookup_value- is the value we are searching for.

It can be a value (number, date, or text) or a cell reference.

  1. Table_array – is the range of cells used to search for the lookup value from which we retrieve the result. The VLOOKUP function always searches in the first column of the table.
  2. Col_index_num – is the number of the column from which to return a value.
  3. Range_lookup (optional) – determines whether to search for an approximate(TRUE) or exact(FALSE) match.

Using Helper Column

In Google sheets, we can use a helper column to join multiple criteria together. The helper column helps to create a unique identifier that allows us to look up a particular value. Let us look at an example below where we use the abbreviation to find a subject in another column.

Look at the table below.

VLOOKUP with multiple criteria using Helper Column 1

Step 1: Let us insert a helper column before Column A. Use the following formula for that column and drag it down using Autofill.

=B2&”-“&C2

You get a column as shown below.

VLOOKUP with multiple criteria using Helper Column 1-1

Step 2: Use the following VLOOKUP formula in cell F3. Press Enter

=VLOOKUP($E3&”-“&F$2,”$A$1:$D$11,4,0)

  • The first argument is a combo of the two values in the table.
  • The second argument specifies the range which is locked.
  • The third specifies the index of the column to be printed.
  • The fourth shows an approximate match.
VLOOKUP with multiple criteria using Helper Column 1-2

Step 3: Drag the formula to cell F4.

VLOOKUP with multiple criteria using Helper Column 1-3

Step 4: Use the below formula for cell G3 and drag it to G4 using Autofill.

=VLOOKUP($E3&”-&“F$2,”A1:D11,4,0)

VLOOKUP with multiple criteria using Helper Column 1-4

Thus, in the VLOOKUP function, we combined two values and used the helper column to identify the name of the degree.

Using Advanced Formulas and Functions

#1 – Using Ampersand to Join multiple criteria in Google sheets

We use the Ampersand symbol to join two values for a search criterion. For example, we have some employee details, including their numbers. We need all employees starting with 201. 

Using Ampersand to Join multiple criteria 1

Step 1: Consider the employee whose number begins with 201. If you do not know the entire number, you can use a wild card character as shown below and concatenate it using &. Let us use the following formula and find the designation of the employee.

=VLOOKUP(201&”*”, $A$1:$C$9, 3, FALSE)

Using Ampersand to Join multiple criteria 1-1

Thus, the formula retrieves the employee’s designation whose employee number begins with 201.

#2 – MATCH Function to Include multiple criteria in Google sheets

In many cases, we may need to search to the left using the VLOOKUP function. To prevent this, we use the INDEX-MATCH function in conjunction with VLOOKUP to get accurate results. It helps Google Sheets look for the column in which to look for the information and returns the correct result, which VLOOKUP uses.

Look at the data below.

MATCH Function to Include multiple criteria 1

To retrieve the data for cell A4 matching the data in column G and returning a match from H, we use this formula.

=INDEX(F2:F6, MATCH(B8,G2:G6,0))

MATCH Function to Include multiple criteria 1-1

As seen above, INDEX finds the lookup value in cell A5 by column numbers, and MATCH provides those numbers.

#3 – IF Function to Join multiple criteria in Google sheets

The VLOOKUP function in Google Sheets helps search values within a table, and the IF statement returns specific values based on conditions we have set. The addition of IF makes the VLOOKUP function more versatile.

Here, we have a table which contains the rainfall details in a city for an entire year. We must check if a particular month has received rainfall above the average rainfall or below. Using just VLOOKUP, you can retrieve the value alone. Combining it with VLOOKUP helps check the rainfall received. Look at the table below.

IF Function to Join multiple criteria 1

Enter the below formula in cell D6.

=IF(VLOOKUP(D5,A2:B13,2,FALSE)>16,”Received above average rainfall”, “Received below average rainfall”)

The above formula checks the month entered in cell D5 and its corresponding rainfall using VLOOKUP.

  • If the rainfall received is above 16 cm, it means that the rainfall of that month is above average.
  • If the rainfall is below 16 cm, it’s below the average.

We have entered June in cell D5. Now, check the result.

IF Function to Join multiple criteria 1-1

#4 – Multiple Criteria in Single Column in Google sheets

Here, we want to combine the search criteria to use in the VLOOKUP function. For example, we have the month and its abbreviation, but our table has both combined in a single column.

Multiple Criteria in Single Column 1

The formula for VLOOKUP with multiple criteria (similar to excel vlookup with multiple criteria) in single columns is straightforward:

=VLOOKUP(A16&” “&B16,$A$2:$B$13,2,FALSE)

Here, we combine the two separate values in cell A16 and B16 using & for concatenation and use it as the first argument.

It’s a regular VLOOKUP formula, with concatenated values as the first argument. We then use it as the search key for the VLOOKUP formula.

Here, A15&” “&B15

combines June in column A and 06 in column B into June 06, which is the value used for the search in the table with VLOOKUP.

Multiple Criteria in Single Column 1-1

#5 – Use of Drop-Down Lists

Sometimes we can use a dropdown list to choose a required lookup_value. Consider the below data.

Use of Drop-Down Lists 1

Step 1: Go to cell D3. Then, go to the Insert tab and choose the Drop-down option.

Use of Drop-Down Lists 1-1

Step 2: We have products’ names. Enter them as options in the drop-down box. Finally, click on Done.

Use of Drop-Down Lists 1-2

Step 3: You can click on cell D3 and choose any of the options like cookies, candy, etc.

Use of Drop-Down Lists 1-3

Step 4: Enter the quantity of the product purchased. Type the following formula in cell E3.

=VLOOKUP(D3,A1:B5,2,1)

Use of Drop-Down Lists 1-4

Step 5: Press Enter. You can now get the total price by typing the following formula in G3.

=E3*F3.

Use of Drop-Down Lists 1-5

Using the drop-down list, we can select the combination of the value and its corresponding cost with VLOOKUP function, which will retrieve the value from the table.

Frequently Asked Questions (FAQs)

What is the limitation of VLOOKUP multiple criteria in Google Sheets?

When using VLOOKUP, there is no left lookup. The search key should be present in the first column of the range specified, and the corresponding result you are looking for should be found in a column to its right. The main limitation of the VLOOKUP function is that it can only search from left to right.

What are the most common mistakes when using VLOOKUP in Google Sheets?

• As the VLOOKUP function has multiple parameters, users are bound to get errors if the parameters are not carefully specified. Some of the most common VLOOKUP mistakes are:
• When you enter incorrect data or an incorrect number format, you get a #N/A error.
• When you supply invalid VLOOKUP cell references, you get the #N/A error.
• If you enter the incorrect column number, you get a #VALUE! Error.
• When the correct syntax is not used, you get an #N/A error as well.

How to use VLOOKUP from a different sheet?

In many situations, we may have the main table and Lookup table, which often reside on different sheets. To refer to another worksheet with your VLOOKUP formula, before you use the reference, just use the worksheet name followed by an exclamation mark (!). For example:

=VLOOKUP(A2,Sheet1!$A$2:$B$11,1,TRUE)

Guide to What is VLOOKUP with Multiple Criteria in Google Sheets. Here we discuss VLOOKUP with multiple criteria in Google sheets using advanced formulas & functions. You can learn more from the following articles. –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *