There can be hardly anyone who has never come across a situation to work on Microsoft Excel. One can go on and on about the advantages of using this software. Right from small businesses to large enterprises MS Excel becomes a go-to app for everyone. Among the many functions, VLOOKUP is one of the prominent functions that has been a time saver for users with a huge amount of data. Can you do a VLOOKUP with 2 criteria or multiple criteria for that matter? Well, we have got you covered. In this article, you will learn how to Use VLOOKUP with multiple criteria using two methods, including using VLOOKUP with multiple Criteria helper columns.
How to Use VLOOKUP with Multiple Criteria
Keep reading further to find the steps explaining how to use VLOOKUP with multiple criteria in detail with useful illustrations for better understanding.
What is VLOOKUP in Excel?
VLOOKUP is the abbreviation for the term Vertical Lookup. It is an in-built function of MS Excel which enables users to look for particular values by searching them across the sheet vertically. This is done by using a simple formula which is:
=VLOOKUP (lookup_value, table_array, col_index_number, [range_lookup])
Here,
- lookup_value: It is the value that you are looking for in the data.
- table_array: It specifies the location of data in which the required values are present.
- col_index_number: It refers to the column number from which we are going to get the return values.
- range_lookup: It consists of two options. The first is TRUE, selecting which means that you are looking for an approximate match. The second is FALSE, selecting which means that you are looking for an exact match.
Also Read: Difference Between Rows and Columns in Excel
Can You Do a VLOOKUP with 2 Criteria?
Yes, you can do a VLOOKUP with 2 criteria or even more criteria. VLOOKUP function can be used in two different methods using multiple criteria. One is VLOOKUP with multiple Criteria helper columns, and the other is VLOOKUP with multiple Criteria using choose function. Read through this article to know about it.
How to Use VLOOKUP with Multiple Criteria?
Let’s proceed with the methods that you can use when wondering How to Use VLOOKUP with Multiple Criteria.
Method 1: Using Helper Columns
It is one of the methods that can be used when you have to look for values in multiple criteria. VLOOKUP with multiple criteria helper columns required you to follow the below-mentioned steps.
Note: In the below-mentioned steps, we will use scores of students in maths and science from terms 1, 2, and 3. The score of Maths in all the terms is compiled together. We will find the score of Maths in each term next to their names in the table on the right-hand side.
1. Open the MS Excel file with the required data.
2. Insert a new column between the two columns that you want to combine.
3. Combine columns B and D using an ampersand (&) and separator (,) with the =B2&”,”&D2 formula.
4. Press the Enter key on your keyboard to see the combined result.
5. Drag the formula to the entire column to combine the rest of the cells.
6. Enter the VLOOKUP formula where you want the scores. You can also view and edit the formula in the formula bar.
7. The lookup_value will include the cell H7 and I6 as a refrence. Write the formula as H7&”,”&I6.
8. Lock the rows and columns accordingly as we need to fill rest of the details as well. Lock column H and row 6 by pressing the F4 key to continue to use VLOOKUP with multiple criteria.
9. Move to the next argument which is table_array by adding a comma (,).
10. Select the rows and columns which contain the required values.
11. Lock the cell reference by pressing the F4 key.
12. Add comma (,) and move to the next argument: col_index_num.
13. Mention the column number from the table array which gives the required value. Here, it is the Maths column which lies 3rd. Type 3 in the formula bar.
14. Type comma (,) to move to the next argument range_lookup.
15. Select the FALSE – Exact match option to proceed to get the right values.
16. Close the bracket after completing the formula.
17. Press Enter to get the first value.
18. Drag the formula through the table to get all the required details.
These steps must have clarified your doubts about the question can you do a VLOOKUP with 2 criteria.
Also Read: How to Copy and Paste Values Without formulas in Excel
Method 2: Using CHOOSE Function
You can also imply this method to use VLOOKUP with multiple criteria using Choose function. Unlike the above-mentioned method, you will not require a helper column here. You can easily use Choose function option to use VLOOKUP with multiple criteria. Follow the below-mentioned steps to use VLOOKUP with multiple criteria.
1. Navigate to the MS Excel file with the required data.
2. Enter the VLOOKUP formula in the required cell.
3. The lookup_value will include the cell G7 and H6 as a reference. Write the formula as G7&”,”&H6.
4. Lock the rows and columns accordingly as we need to fill rest of the details as well. Lock column G and row 6 by pressing the F4 key.
5. Type comma (,) to move to the next argument.
6. Here instead of the table_array, use the CHOOSE function.
7. Type 1,2 in curly brackets as index_num to create a combination.
8. Type comma (,) to move to the next argument value1.
9. Select the value1 that is going to be the name column and lock the values by pressing the F4 key.
10. To combine value1 with the next column, add an ampersand (&) with a Separator (,) followed by selecting value2 which is the term column.
11. Lock the values by pressing the F4 key and add a comma (,) to move to the next argument.
12. To add value2, select the Maths column which is required as a result, and lock the values by pressing the F4 key.
13. Close the bracket to complete CHOOSE function. Now you have table_array without a helper column.
14. Type comma (,) and move to the col_index_num argument, mention 2 as Maths is the 2nd column from the source column.
15. Add comma (,) to move to the range_lookup argument and select FALSE to get the exact value.
16. Close the bracket and press Ctrl+Shift+Enter to get the result.
17. Drag the formula through the table and get the complete result.
This was the method to do VLOOKUP with multiple criteria using CHOOSE function.
Frequently Asked Questions (FAQs)
Q1. What is the need to add a separator while making combinations?
Ans. When we make combinations without using separators, there are chances that we end up getting the same combinations for different values. For example,
| 1 | 2 | With separator | Without separator |
| abc | 123 | abc,123 | abc123 |
| abc1 | 23 | abc1,23 | abc123 |
Therefore, it is always recommended to use a separator to avoid such confusion.
Q2. Is it necessary to add the helper column in between the data?
Ans. No, you can also add the helper column towards the extreme right or left if you do not want to make any changes to the original data. But inserting it in between the columns enables you to add just two columns to the table array instead of four or more. You can work according to your convenience.
Recommended:
These were the two methods that you can imply while using VLOOKUP with multiple criteria. We hope that this guide was helpful and you were able to learn about how to use VLOOKUP with multiple criteria and VLOOKUP with multiple Criteria helper columns. Feel free to contact us with your queries and suggestions via the comments section below. Also, let us know what you want to learn about next.
