How to Use VLOOKUP with Multiple Criteria


How to Use VLOOKUP with Multiple Criteria

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

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.

Open the MS Excel file with the required data.

2. Insert a new column between the two columns that you want to combine.

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.

Here, we will be combining the two columns | How to Use VLOOKUP with Multiple Criteria

4. Press the Enter key on your keyboard to see the combined result.

Press Enter to see the combined result

5. Drag the formula to the entire column to combine the rest of the cells.

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.

Enter the VLOOKUP formula where you want the scores.

7. The lookup_value will include the cell H7 and I6 as a refrence. Write the formula as H7&”,”&I6.

The lookup_value will include the cell H7 and I6 as a refrence. | How to Use VLOOKUP with Multiple Criteria

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.

Lock the rows and columns accordingly as we need to fill rest of the details as well.

9. Move to the next argument which is table_array by adding a comma (,).

Move to the next argument which is table_array by adding a comma (,)

10. Select the rows and columns which contain the required values.

Select the rows and columns which contain the required values | How to Use VLOOKUP with Multiple Criteria

11. Lock the cell reference by pressing the F4 key.

Lock the cell reference by pressing the F4 key

12. Add comma (,) and move to the next argument: col_index_num.

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.

Mention the column number from the table array which gives the required value. | How to Use VLOOKUP with Multiple Criteria

14. Type comma (,) to move to the next argument range_lookup.

Type comma (,) to move to the next argument range_lookup

15. Select the FALSE – Exact match option to proceed to get the right values.

Select the FALSE-Exact match option to proceed to get the right values

16. Close the bracket after completing the formula.

Close the bracket as we have completed the formula

17. Press Enter to get the first value.

Press Enter to get the first value | How to Use VLOOKUP with Multiple Criteria

18. Drag the formula through the table to get all the required details.

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.

Open the MS Excel file with the required data

2. Enter the VLOOKUP formula in the required cell.

Enter the VLOOKUP formula in the required cell | How to Use VLOOKUP with Multiple Criteria

3. The lookup_value will include the cell G7 and H6 as a reference. Write the formula as G7&”,”&H6.

The lookup_value will include the cell G7 and H6 as a reference.

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.

Lock the rows and columns accordingly as we need to fill rest of the details as well.

5. Type comma (,) to move to the next argument.

Type comma (,) to move to the next argument | How to Use VLOOKUP with Multiple Criteria

6. Here instead of the table_array, use the CHOOSE function.

Here instead of the table_array we will be using the CHOOSE function

7. Type 1,2 in curly brackets as index_num to create a combination.

Type 1,2 in curly brackets as index_num to create a combination

8. Type comma (,) to move to the next argument value1.

Type comma (,) to move to the next argument value1 | How to Use VLOOKUP with Multiple Criteria

9. Select the value1 that is going to be the name column and lock the values by pressing the F4 key.

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.

To combine value1 with the next column, add an ampersand (&) with a Separator (,)

11. Lock the values by pressing the F4 key and add a comma (,) to move to the next argument.

Lock the values by pressing the F4 key and add a comma (,) to move to the next argument | How to Use VLOOKUP with Multiple Criteria

12. To add value2, select the Maths column which is required as a result, and lock the values by pressing the F4 key.

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.

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.

Type comma (,) and move to the col_index_num argument, mention 2 as Maths is the 2nd column from the source column | How to Use VLOOKUP with Multiple Criteria

15. Add comma (,) to move to the range_lookup argument and select FALSE to get the exact value.

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.

Close the bracket and press Ctrl+Shift+Enter to get the result

17. Drag the formula through the table and get the complete result.

Drag the formula through the table and get the entire result | How to Use VLOOKUP with Multiple Criteria

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.

Jassweb always keeps its services up-to-date with the latest trends in the market, providing its customers all over the world with high-end and easily extensible internet, intranet and extranet products.

Resources

Blog

Developer Center

Exchange

FAQ

Contact

[email protected]

+919878044183