How to Use VLOOKUP with Multiple Criteria in Google Sheets [Solved]

0
92

First of all let me clarify one thing. VLOOKUP with multiple criteria is possible in Google Sheets! There are two methods to deal with 2 or more criteria or also called search keys in VLOOKUP usage in Google Doc Spreadsheets. I am using Two Criteria here in this example. Also the tips are included to use three or more criteria as shown on the above featured image.

Here is that two approaches to deal with multiple criteria in VLOOKUP formula.

1. Adding an Additional Column to your Data – Simple Approach.

2. Without Adding Any Additional Columns or Modifications but Using the Vlookup Formula with Array – Advanced Vlookup Use and Recommended.

The Simple Approach to Vlookup with Multiple Criteria

Here is my Sample Data. You can mimic the data to follow this tutorial.

Sample Date for Vlookup with Multiple Criteria

Hope you already know how to use VLOOKUP formula in Google Doc Spreadsheet. Here is a simple example.

=vlookup(“Safety Helmet”,A5:G12,7,0)

The above VLOOKUP formula will lookup criterion or search key “Safety Helmet” in the first column in the range A5:G12, i.e. column A, and return the corresponding value from column index 7, i.e. column G. Above I’ve used the criteria directly within the formula. When you refer the criteria or search key to a cell, it will be as below.

Google Sheets VLOOKUP with One Criteria

In the above example, VLOOKKUP search for only one criterion that is “Safety Helmet” in the first column. Now I want VLOOKUP to lookup two criteria in the first two columns. How to do that?

Here is our simple approach.

VLOOKUP formula with Multiple Criteria - Simple Approach

See the above example. Here there are two criteria which Vlookup has to lookup. What are they? Criteria # 1 you can see in Cell A2, i.e., “Safety Helmet” and Criteria # 2 is “North East” in Cell B2.

To solve this, I added an additional column labelled as “Additional lookup Column” in our data. It’s the present Column A. In this column we joined the cell values in Column B an Column C.

Then in our VLOOKUP formula, I combined criterion in Cell A2 and B2. Hope you’ve got this idea.

In Google Sheets, there is a better solution. Without adding any additional column you can use VLOOKUP in Google Sheets for multiple criteria LOOKUP.

VLOOKUP with Multiple Criteria in Google Sheets Using Array

This is the recommended method to deal with multiple criteria in Google Sheets.

Before going to this trick, I request you to go through our usage tips of ArrayFormula, IFERROR and Curly Braces then come back here. Because I am going to nest all these formulas with VLOOKUP. If you know these functions, you can directly move to the below steps.

VLOOKUP with Multiple Criteria in Google Sheets using Array

The above is the example to multiple criteria usage with Array and Vlookup combination in Google Sheets. I will explain this formula so that you can use it in any other similar case.

=ArrayFormula(iferror(vlookup(A2&B2, {A5:A&B5:B, C5:G}, 6, 0 ), 0))

You only need to take care about the Red coloured VLOOKUP formula. The other formula part, you can easily learn from our Functions Guide as mentioned above.

Formula Explanation Part:

vlookup(A2&B2

Here I’ve joined the two criteria in cell A2&B2.

{A5:A&B5:B, C5:G}

In order to understand this part, you should just apply this as below in any cell.

=ArrayFormula({A5:A&B5:B, C5:G})

It will pull the data as below which is our lookup range.

Hope you now understand how to use VLOOKUP with Multiple Criteria in Google Sheets. The above example is with two criteria. When there are more than two criteria, you can modify the formula as below.

=ArrayFormula(iferror(vlookup(A2&B2&C2, {A5:A&B5:B&C5:C, D5:G}, 5, 0 ), 0))

Here my criteria is as below. I’ve included date also in this criteria.

VLOOKUP with three criteria in Google Sheets

Thus you can develop the VLOLOKUP formula with multiple criteria or lookup values. Share if you like. Thanks.

LEAVE A REPLY

Please enter your comment!
Please enter your name here