Use VLOOKUP with Multiple Criteria in Excel (6 Methods + Alternatives)

If you have worked with Microsoft Excel for a while, you know how functional the VLOOKUP function is. We can search for an element from a large dataset with criteria. It saves a lot of time to find a particular value. But, many of you may be confused to use it with several conditions. In this tutorial, you will learn to use VLOOKUP with multiple criteria in Excel. This tutorial will be on point with suitable examples and proper illustrations.

Download Practice Workbook

Download this practice workbook.

6 Effective Ways to Use VLOOKUP with Multiple Criteria in Excel

In this section, we will provide you with seven effective techniques for the VLOOKUP function with multiple criteria. If you are facing a lot of trouble using VLOOKUP with multiple criteria, this article will solve that with ease. We recommend you learn and apply all these methods to your dataset. It will undoubtedly enhance your Excel knowledge.

To demonstrate this tutorial, we are going to use this dataset:

As you can see, we have a dataset of Movies. We have some information about the movies. Now, we will use the multiple criteria from this dataset and find results according to that.

1. Using Helper Column to Use VLOOKUP  with Multiple Criteria

In this method, we are using VLOOKUP with a helper column. You can also call it solving by an ampersand (&). Remember, VLOOKUP always looks for values from the first column. So, to solve this, we will make our helper column the first column.

Basically, when you choose the criteria, join them with an ampersand (&) sign. This value should be on the helper column.

For example, we are using Movie and Genre as the condition. So, our helper column will be a combination of Movie and Genre. Our goal is to find the Release name based on the criteria Movie and Genre.

To understand it better, follow the simple steps below.

📌 Steps

① First, create a new column “Helper” to the left.

② Then, type the following formula in Cell B5:

③ Then, Click Enter.

④ Now, drag the Fill handle icon to copy it across the column.

⑤ Next, type the following formula in Cell C14:

Here, we are using VLOOKUP to search for the Movie Titanic and Genre of Romantic.

⑥ Now, press Enter.

As you can see, we are successful in using the VLOOKUP function with multiple criteria in Excel.

Read More: VLOOKUP with Multiple Criteria and Multiple Results (8 Examples)

2. Use of VLOOKUP with CHOOSE Function in Excel

Don’t want to use any helper column? Use this method where we are going to combine the VLOOKUP function with the CHOOSE function. Though this formula looks complex, it will be clear once you understand this.

The CHOOSE function returns a value from the list of value arguments. In the function, we will have some values and an index number. According to that index number, it will return the value.

Syntax of CHOOSE function:

Our goal is to find the Movie name with the Genre Action and Release Year of 2014.

📌 Steps

① First, type the following formula in Cell C14:

② Then, press Enter.

Here, the CHOOSE function will form a table of columns Genre & Release Year. After that, it combined the column Genre and Release Year as a single column for the VLOOKUP function.

Finally, we are successful in using VLOOKUP with multiple criteria in Excel.

Read More: Vlookup with Multiple Criteria without a Helper Column in Excel (5 Ways)

3. VLOOKUP with MATCH Function to Combine  Multiple Criteria

Now, in this method, we are combining the VLOOKUP function with the MATCH function. The MATCH function returns a relative position of an item in an array that fits a particular value in a specified order. By combining the VLOOKUP with the MATCH function in one formula, We can specify the output types manually by merging the VLOOKUP with the MATCH function.

Here, we are also using the helper column. The helper column is not as bad as you might think. It can help you solve a lot of problems in the VLOOKUP function.

Here, our goal is to find the Release Year based on Genre and Movie.

📌 Steps

① First, create a Helper column in Column B.

② Then, type the formula in Cell B5: =C5&D5. Press Enter. Then, Drag the Fill Handle icon over the range of cells B6:B9

③  Here, you created a Helper column. Now, type the following formula in Cell C14:

④ Then, press Enter.

In this formula, the MATCH function looks for the value present in Cell B14 in the array of B4:E4 and then returns the column number. And this column number is then transferred to the third argument (col_num_index) of the VLOOKUP function.

In the end, we are successful in using the  VLOOKUP function with multiple criteria.

Read More: How to VLOOKUP from Multiple Columns with Only One Return in Excel (2 Ways)

4. Combination of VLOOKUP and IF Function in Excel

Now, the previous method was using the helper column. If you want to avoid that, you can use this method. It is pretty easy to use. We are using the VLOOKUP along with the IF function.

The IF function will set up the lookup array for the VLOOKUP function.

From this dataset, our goal is to extract the Release Year of the Movie John Hitch and Genre of Comedy.

📌 Steps

① First, type the following formula in Cell C14:

② Then, press Enter.

As you can see, we successfully used VLOOKUP with multiple criteria and extracted the Release Year.

Read More: Example of VLOOKUP with Multiple IF Condition in Excel (9 Criteria)

Similar Readings

• VLOOKUP with Numbers in Excel (4 Examples)
• VLOOKUP Not Working (8 Reasons & Solutions)
• How to Use VLOOKUP with Multiple Criteria in Different Sheets
• Excel VLOOKUP to Find Last Value in Column (with Alternatives)
• How to Vlookup and Sum Across Multiple Sheets in Excel (2 Formulas)

5. VLOOKUP with Multiple Criteria for a Single Column in Excel

Up to this point, we were using the VLOOKUP with numerous criteria that come from the different columns. Now, what if the conditions come from the same single column? Let’s see.

Here, we have to use our LOOKUP values as a range in the first argument. Then, it will search it from the lookup_array. 

Now, our goal is to find the name of the Genre-based on the Movie.

📌 Steps

① First, type the following formula in Cell C14:

② Then, press Enter.

As you can see, we extracted multiple data from the single-column using VLOOKUP with multiple criteria.

Read More: How to VLOOKUP with Multiple Conditions in Excel (2 Methods)

6. Drop-down Lists as Multiple Criteria in VLOOKUP

Now, you won’t see people using this method too often. But, in my opinion, it is pretty simple to use. If you love to work with the user interface, this method is definitely for you.

If you create a drop-down menu, you don’t have to input your values manually. Rather, you can easily select them from your drop-down menu. After you have entered the VLOOKUP, it will show the corresponding values.

Here, our goal is to find the Release Year based on Movie and Genre.

📌 Step 1: Select Data Validation 

➤ First, select Cell D11.

➤ Then, go to the Data tab.

➤ After that, select Data Validation.

📌 Step 2: Create Your Drop-down List

➤ Now, from the Data Validation dialog box, select List from the drop-down menu.

➤ Then, in the Source Field, select range of cells B5:B9

➤ After that, click OK. It will create the drop-down for the Movie field.

➤ Now, do the same for the Genre.

📌 Step 3: Apply VLOOKUP Function

➤ Now, type the following formula in Cell C14:

➤ Then, press Enter.

As you can see, we have successfully used VLOOKUP with multiple criteria with the help of the data validation tool of Excel.

Read More: VLOOKUP with Drop Down List in Excel

2 Alternatives to VLOOKUP with Multiple Criteria

Here, we are providing you with another two alternative methods that you can implement into your worksheet. These two methods will provide you with similar results to the VLOOKUP function. Hope you will learn and apply these methods too.

1. Use of LOOKUP Function to Work Like VLOOKUP in Excel

The LOOKUP function can help you solve this problem. You can use this to work like VLOOKUP. Though this method is complex, you can keep this method in your arsenal. Who knows, it may come in handy in the future.

Here, our goal is to find the Release Year collection based on Movie and Genre.

📌 Steps

① First, type the following formula in Cell C14:

② Then press Enter.

As you can see, this function works the same as the VLOOKUP function.

Read More: Excel LOOKUP vs VLOOKUP: With 3 Examples

2. Use of INDEX and MATCH Function to Work Like VLOOKUP

Now, you may ask why INDEX-MATCH? Basically, these functions are also commonly used in Excel. We are combining the INDEX function and the MATCH function to make it work like the VLOOKUP function. It will give you the same output. I hope learning this method will come in handy near future.

Here, our goal is to find the Release Year collection based on Movie and Genre.

📌 Steps

① First, type the following formula in Cell C14:

② Then press Enter.

As you can see, we used the INDEX-MATCH functions like the VLOOKUP with multiple criteria.

Read More: INDEX MATCH vs VLOOKUP Function (9 Examples)

💬 Things to Remember

✎ When using the Helper column, it must contain the criteria in a concatenated form.

✎ VLOOKUP for a single column will return the first match from the criteria. So, if your dataset contains identical values, it will return only the first one.

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to use VLOOKUP with multiple criteria in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Don’t forget to check our website ExcelDemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!

Related Articles

• Use of VBA VLOOKUP to Find Values from Another Worksheet in Excel
• VLOOKUP to Return Multiple Columns in Excel (4 Examples)
• How to Use VLOOKUP for Multiple Columns in Excel
• Excel VLOOKUP with Multiple Criteria in Horizontal & Vertical Way
• Excel VLOOKUP with Multiple Criteria in Column and Row
• VLOOKUP with Multiple Criteria Including Date Range in Excel (2 Ways)
• How to Use VLOOKUP Function to Compare Two Lists in Excel