How to do a VLOOKUP with multiple conditions or criteria (3 methods)

Podcast: Play in new window | Download

Subscribe: Spotify | TuneIn | RSS

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:

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

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

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:

So we have a list of 0s and 1s, now we need to join it with the actual list of values we want to return from our VLOOKUP function (the Fuel_Type column). This is where the left and right brackets come into play. Notice how there is a left bracket to the left of ARRAYFORMULA and a right bracket after the reference to E2:E302. That range is all the values in the Fuel_Type column. So what we get from this part of the formula {arrayformula((A2:A302=I5)*(B2:B302=I6)*(D2:D302=I7)),E2:E302} is a list of 0s and 1s on the left, and a list of fuel types on the right:

This is a list we are manually creating in Google Sheets to feed into the VLOOKUP formula. The VLOOKUP looks for the “1” in this 2-column list and returns the 2nd column from our fabricated list. In this case, the Fuel Type that meets all 3 conditions is “Petrol.”

Method #2: Using INDEX, MATCH, and INDEX

If you know advanced formulas in Excel/Google Sheets, you’re probably familiar with INDEX and MATCH as an alternative to VLOOKUP. I think this method is slightly easier than the 1st method since it utilizes INDEX and MATCH in a way we are used to. This second method uses a combination of INDEX and MATCH to get the Fuel_Type from column E that match all the conditions in column I:

=index(E2:E302,match(1,index((A2:A302=I13)*(B2:B302=I14)*(D2:D302=I15),0,1),0))

While this formula is still advanced, it doesn’t require the knowledge of ARRAYFORMULA. It’s just a clear use of the INDEX function since this function can accept arrays as a parameter.

Explaining the formula for method #2

The key part of this formula is the index((A2:A302=I13)*(B2:B302=I14)*(D2:D302=I15),0,1) portion. Similar to method #1, you multiple each condition that you want to filter your list on. Our conditions are in cells I13:I15. What is the purpose of wrapping this in the INDEX function? As stated earlier, the INDEX function can accept an array as the first parameter. So that first parameter is an array of 0s and 1s (just like we saw in method #1). Then the 2nd parameter “0” and 3rd parameter “1” simply tells Google Sheets to return the entire column back as the result. There is only one column in our list so that’s why we simply need that “1” as the 3rd parameter.

From here, the rest of the formula should be pretty easy to understand if you’ve used INDEX and MATCH as a substitute for VLOOKUP. The MATCH function looks for the number “1” in the list of 0s and 1s. The outer INDEX function looks at the Fuel_Type column (column E) and returns back the value based on the result of the MATCH function.

Method #3 (Preferred): Combining multiple columns to create a unique “key” column

I like this method because it’s the easiest to understand and implement. You’ve probably concatenated multiple columns before to create a unique key in a list. It’s kind of a hack to create a unique ID when your data has multiple columns that have the same values. Some people might think this is a hack and it totally is! I’d say if your dataset and workflow match these three conditions, it’s ok to use this method when you want to do a VLOOKUP with multiple criteria:

  • You have edit access to the Excel file or Google Sheet and can add additional columns
  • Your dataset doesn’t change much
  • The number of columns your are concatenating (e.g. the number of criteria) won’t change much

When you’re doing one-off analysis, you’ll typically meet the above conditions and I think it’s “safe” to use this method.

=vlookup(J22&J23&J24,A2:H302,6,0)

Explaining the formula for method #3

There are multiple ways to optimize this formula. You could add named ranges, reference all of columns A and H, and more. Remember: we are doing a quick one-off analysis, so let’s just find the easiest and quickest way to get our answer. All we have is a simple VLOOKUP formula and nothing else. The “key” is the first parameter in our formula.

In the screenshot above, notice how our conditions are now in column J. I added a helper column by inserting a blank new column in column A. That helper column is a combination of columns B, C, and E. These happen to be the columns we care about since they are the columns that contain our conditions. So in cell A2, the formula would be =B2&C2&E2. You could also do =CONCATENATE(B2,C2,E2) but I like the formula with the ampersand “&” because it’s just easier.

When you do the VLOOKUP, you’re simply looking for the combination of cells J22, J23, and J24 in column A. In theory, column A should be a unique list of “IDs” since you are combining three different columns together. The rest of the VLOOKUP formula should be simple to understand.

Advanced Excel formulas and features class

In this episode, you learned about some advanced formulas for looking up multiple conditions. I recently published a class on Skillshare where I teach advanced formulas like the ones mentioned in this episode. The class is called Excel: Advanced Formulas & Features to Create Efficient Team Workflows. Take your Excel skills to the next level and help your team make better business decisions by taking this class! You’ll learn things like NPV, IRR, and how to use the advanced features in Excel like Goal Seek.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting: