Sajid Ahmed
We are familiar with using VLOOKUP with single criteria but sometimes we need to do it with multiple criteria. There are many ways to VLOOKUP with multiple criteria in google sheets. But we’ll use the VLOOKUP function and other functions to show 4 easy methods to VLOOKUP with multiple criteria in Google Sheets by clear images and steps.
A Sample of Practice Spreadsheet
You can download Google Sheets from here and practice very quickly.
4 Simple Methods to VLOOKUP with Multiple Criteria in Google Sheets
First, let’s get introduced to our dataset. Here we can see the names and emails of some people. Now we will see how the VLOOKUP function works in multiple criteria.
1. Single Column VLOOKUP with Multiple Criteria in Google Sheets
In the first method, we will use the VLOOKUP function to match multiple criteria into a single column. This method is useful when we have a single-column dataset but want to get results for two combined data sets. In this case, we have the first name and last name. We will find the corresponding email address from our data set.
Steps:
- Type the following formula in Cell C14–
=VLOOKUP(C12&" "&C13,$B$5:$C$10,2,false)- Hit Enter button to get the result automatically. If you write any first name in Cell C12 and the last name in Cell C13, you will get their email quickly.
2. VLOOKUP for Multiple Criteria Using Helper Column
Moreover, we can also use a helper column to VLOOKUP multiple criteria if we have a different data set. This method is contrary to the previous process. Because using this method we can get results for a single column of data if we have data in two different columns. Like in this dataset we have the first names and last names separately. So we have to create a helper column by using the CONCATENATE function.
Steps:
- Apply the following formula in Cell D5-
=CONCATENATE(B5," ",C5)- Press Enter button and you will get the names together.
- Then assign the Fill Handle tool to fit the formula in the whole column.
- You will get the names together in the whole column.
- Now we’ll apply the VLOOKUP function to extract the final output, so write the following formula in Cell C13–
=VLOOKUP(C12,$D$5:$E$10,2)- Press Enter button and you will finally get the corresponding email address.
3. Merge ARRAYFORMULA and VLOOKUP Functions for Multiple Criteria
Alternatively, we can use the ARRAYFORMULA function to find multiple criteria in google sheets. This method is much easier, unlike the previous method. We can get results with just one click.
Steps:
- Type the following formula in Cell D13–
=ARRAYFORMULA(VLOOKUP(B13&" "&C13, {$B$5:$B$10&" "&$C$5:$C$10, $D$5:$D$10},2,false))- Hit Enter button to get the result.
Formula Breakdown:
- VLOOKUP(B13&” “&C13,{$B$5:$B$10&” “&$C$5:$C$10,$D$5:$D$10},2,false)
Firstly, the VLOOKUP function will search for the first name in Column B and the last name in Column C together and give the value of the email in Column D.
- ARRAYFORMULA(VLOOKUP(B13&” “&C13,{$B$5:$B$10&” “&$C$5:$C$10, $D$5:$D$10},2,false))
Finally, the ARRAYFORMULA function will perform multiple calculations together and give the correct result.
4. Apply FILTER Function to VLOOKUP with Multiple Criteria in Google Sheets
We can also apply the FILTER function instead of VLOOKUP to get multiple criteria in google sheets very easily. This method is handy because the formula is straightforward.
Steps:
- Apply the following formula in Cell D13–
=FILTER($D5:$D10,B13=$B5:$B10,C13=$C5:$C10)- Hit Enter button to get the result. If you change the first and last name, you will get the corresponding email automatically.
Conclusion
That’s all for now. Thank you for reading this article. In the article, you will find 4 useful methods to VLOOKUP with multiple criteria in Google Sheets. If you have any queries please leave a comment in the comment section. You will also find different articles related to google sheets on our officewheel.com. Visit the site and explore more.
