Tech 

How to use VLOOKUP with multiple criteria

There is probably no person who has never encountered the situation of working in Microsoft Excel. There are endless benefits of using this software. From small businesses to large enterprises, MS Excel is becoming an application for everyone. Among many features, the VLOOKUP feature is one of the most important features that saves time for users with huge amounts of data. Can you do a VLOOKUP with 2 criteria or more criteria? Well, we’ve 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 support columns.

How to use VLOOKUP with multiple criteria

Read on to find steps explaining how to use VLOOKUP with multiple criteria, detailed with helpful illustrations for better understanding.

What is VLOOKUP in Excel?

VLOOKUP stands for Vertical Lookup. This is a built-in function of MS Excel that allows users to search for specific values ​​by vertically searching the sheet. This is done using a simple formula that reads:

= VLOOKUP(lookup_value, table_field, column_index_number, [range_lookup])

Here,

  • lookup_value: This is the value you are looking for in the data.

  • table_array: Specifies the location of the data where the required values ​​are located.

  • col_index_number: Refers to the number of the column from which we will get the returned values.

  • range_lookup: Consists of two options. The first is TRUE, which means you’re looking for an approximate match. The second is FALSE, selecting means you are looking for an exact match.

Can you do a VLOOKUP with 2 criteria?

Yes, you can do a VLOOKUP with 2 or even more criteria. The VLOOKUP function can be used in two different ways using multiple criteria. One is VLOOKUP with support columns for multiple criteria and the other is VLOOKUP with multiple criteria using select function. Read this article to find out.

How to use VLOOKUP with multiple criteria?

Let’s move on to the methods you can use when you understand how to use VLOOKUP with multiple criteria.

Method 1: Using support columns

This is one method you can use when you need to search for values ​​based on multiple criteria. A VLOOKUP function with multiple columns to support criteria required the following steps.

Note: In the following steps, we will use the results of students in mathematics and natural sciences from semesters 1, 2 and 3. The results in mathematics for all semesters are added together. You will find their math results each semester next to their names in the table to the right.

1. Open the MSExcel file with the required data.

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

3. Join columns B and D using the & sign (&) and separator (,) with the formula =B2&”,”&D2.

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

5. Drag the formula across the entire column to merge the remaining cells.

6. Enter the VLOOKUP formula where you want to get the results. You can also view and edit the formula in the formula bar.

7. Lookup_value will contain cell H7 and I6 as a reference. Save the formula as H7&”,”&I6.

8. Lock the rows and columns accordingly as we need to fill other details as well. Lock column H and row 6 by pressing F4 to continue using VLOOKUP with multiple criteria.

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

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

11. Lock the cell reference by pressing F4.

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

13. Enter the column number from the spreadsheet table that gives the required value. Here, the math column is in third place. Type 3 in the formula bar.

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

15. Select FALSE – Exact Match to get exact values.

16. After completing the formula, close the parentheses.

17. Press Enter to get the first value.

18. Drag the formula through the table to get all the details you need.

These steps must have cleared your doubts about whether you can do a VLOOKUP with 2 criteria.

Method 2: Using the SELECT function

You can also suggest that this method use VLOOKUP with multiple criteria using Select. Unlike the method mentioned above, you won’t need an auxiliary column here. You can easily use the Select Function to use VLOOKUP with multiple criteria. Follow the steps below to use VLOOKUP with multiple criteria.

1. Go to the MS Excel file with the required data.

2. Enter the VLOOKUP formula in the desired cell.

3. Lookup_value will contain cells G7 and H6 as a reference. Save the formula as G7&”,”&H6.

4. Lock the rows and columns accordingly as we need to fill other details as well. Lock column G and row 6 by pressing F4.

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

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

7. Type 1,2 in curly brackets as index_number to make the combination.

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

9. Select value1 to be the name column and lock the values ​​by pressing F4.

10. To join value1 with the next column, add an ampersand (&) followed by a separator (,), and then select value2, which is the expression column.

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

12. To add value2, select the required Math column as output and lock the values ​​by pressing F4.

13. Close the parenthesis to complete the SELECT function. You now have a table_array without an auxiliary column.

14. Type a comma (,) and go to the col_index_num argument, mention 2 because Math is the second column from the original column.

15. Add a comma (,) to go to the range_lookup argument and choose 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 full result.

This was a multi-criteria VLOOKUP method using the SELECT function.

Frequently Asked Questions (FAQ)

P1. What is the need to add dividers when creating a combination?

answer When we create combinations without using separators, there is a possibility that we will get the same combinations for different values. For example,

1
2
With separator
No 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 an auxiliary column between the data?

answer No, you can also add an auxiliary column to the far right or left if you don’t want to change the original data. But inserting between columns allows you to add only two columns to a table field instead of four or more. You can do as you see fit.

***

These are two methods that can be suggested when using VLOOKUP with multiple criteria. We hope you found this tutorial helpful and that you were able to learn how to use VLOOKUP with multiple criteria and VLOOKUP with multiple criteria helper columns. Feel free to reach out to us with questions and suggestions via the comments section below. Also, tell us what you want to know next.

Related posts

Leave a Comment