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:

dataset of vlookup multiple criteria

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.

Using Helper Column to Use VLOOKUP  with Multiple Criteria

Then, type the following formula in Cell B5:

=C5&D5

Then, Click Enter.

Using Helper Column to Use VLOOKUP  with Multiple Criteria

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

Using Helper Column to Use VLOOKUP  with Multiple Criteria

Next, type the following formula in Cell C14:

=VLOOKUP(D11&D12,B5:E9,4,FALSE)

vlookup formula in excel

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

Now, press Enter.

output of Using Helper Column to Use VLOOKUP  with Multiple Criteria

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:

CHOOSE(index_num, value1, [value2], …)

Example:

CHOOSE(2,”Ball”,”Book”,”Pen”) will return “Book“.

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:

=VLOOKUP(D11&$D$12,CHOOSE({1,2},D5:D9&C5:C9,B5:B9),2,FALSE)

vlookup with multiple criteria

Then, press Enter.

output of Use of VLOOKUP with CHOOSE Function in Excel

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.

Using Helper Column to Use VLOOKUP  with Multiple Criteria

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

VLOOKUP with MATCH Function to Combine  Multiple Criteria

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

=VLOOKUP(D11&D12,B5:E9,MATCH(B14,B4:E4,0),FALSE)

Then, press Enter.

output of VLOOKUP with MATCH Function to Combine  Multiple Criteria

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:

=VLOOKUP(D11, IF(C5:C9=D12, B5:D9, ""),3, FALSE)

vlookup formula with multiple criteria

Then, press Enter.

output of Combination of VLOOKUP and IF Function in Excel

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


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:

=VLOOKUP(D11:D12,B5:D9,2,FALSE)

Then, press Enter.

output of VLOOKUP with Multiple Criteria for a Single Column in Excel

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.

select the cell

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.

Drop-down Lists as Multiple Criteria in VLOOKUP

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

range of cells

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

Drop-down Lists as Multiple Criteria in VLOOKUP

Now, do the same for the Genre.

📌 Step 3: Apply VLOOKUP Function

Now, type the following formula in Cell C14:

=VLOOKUP(D11, IF(C5:C9=D12, B5:D9, ""), 3, FALSE)

VLOOKUP formula in Excel

Then, press Enter.

output of Drop-down Lists as Multiple Criteria in VLOOKUP

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:

=LOOKUP(2,1/(B5:B9=D11)/(C5:C9=D12),(D5:D9))

lookup function in excel

Then press Enter.

Alternatives to VLOOKUP with Multiple Criteria

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

Note:
  • B5:B9=D11: It will lookup for the value D11 from the range B5:B19.
  • C5:C9=D12: It will lookup for the value D12 from the range C5:C9.
  • D5:D9: Finally, the LOOKUP function will extract the output from this range.

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:

=INDEX(D5:D9,MATCH(1,(B5:B9=D11)*(C5:C9=D12),0))

Alternatives to VLOOKUP with Multiple Criteria

Then press Enter.

Alternatives to VLOOKUP with Multiple Criteria

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

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo