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:
- 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:
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:
- SyntaxFM #477: Tips for Communicating
Trackbacks/Pingbacks
[…] While we’re at it, I’d say CHOOSECOLS() is equally as useful because you can just pull out the columns of data that matter for you. In this case, you can just pull out the list of students and just the scores from the subjects that matter for you. This feels like a more user-friendly version of the {} syntax for concatenating different ranges to create a custom range (typically used for creating a custom VLOOKUP formula with multiple conditions) […]