Once you learn the VLOOKUP formula, your world opens up in terms of being able to analyze and manipulate data. There are hundreds if not thousands of tutorials on how to use the VLOOKUP formula since it's such a powerful formula for finding the data you need in a long list. Comparable formulas include the combination of INDEX and MATCH and the new XLOOKUP formula which took the Excel world by storm. One common task you might need to do as an analyst is find data based on multiple conditions or criteria. VLOOKUP only allows you to lookup one specific value or criteria in a list of data. In this episode, I'll describe three methods for doing a VLOOKUP when you have multiple conditions or criteria. These methods utilize more advanced formula features, and the third method is my favorite. Copy this Google Sheet to see the different methods in action.

Video tutorial of this episode:

https://www.youtube.com/watch?v=OYQm7XHCZoA

Method #1: Creating a new array with ARRAYFORMULA and brackets

The first method is quite advanced and requires knowledge of the following:

* Boolean logic* The ARRAYFORMULA function in Google Sheets* Brackets {} for creating arrays in Google Sheets

In Excel, you can do something similar to the ARRAYFORMULA function in Google Sheets by pressing CTRL+SHIFT+ENTER when entering a formula in a cell. It's not the most intuitive way of entering a formula. If you have Office 365, you actually don't have to know how to use this keyboard shortcut at all.

This is not my preferred method for doing a VLOOKUP with multiple conditions but it is more scalable than my favorite method (method #3). In terms of the dataset, we have a list of cars and we want to find the Fuel_Type for a "ciaz" car made in "2015" and has "15,000" kilometers on the car (see the highlighted yellow row in the screenshot above). The reason we need to do a VLOOKUP with multiple conditions in this case is because there are multiple rows with a car name "ciaz" made in different years.

Explaining the formula for method #1

Let's take a look at the formula and work inside out to see how this formula works:

=vlookup(1,{arrayformula((A2:A302=I5)*(B2:B302=I6)*(D2:D302=I7)),E2:E302},2,0)

The stuff inside the ARRAYFORMULA is a way to compare everything in the list to the Car Type, Year, and Kms_Driven defined in cells I5:I7. The syntax is a bit weird since you're multiplying each condition to get the row that matches all the conditions. In plain English, it reads something like this:

Find rows where the Car_Name is "ciaz" AND the Year is "2015" AND the Kms_Driven is "15,000"

The reason you need to wrap this in an ARRAYFORMULA is because you are telling Google Sheets to look at all cells in a column (an array) instead of one cell at a time. The "result" of the ARRAYFORMULA is a list of 0s and 1s where the 1s represent rows that meet all the conditions: