Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use VLOOKUP with Multiple Criteria in Different Sheets

This tutorial will demonstrate how to use VLOOKUP with multiple criteria in different sheets in excel. We will use the VLOOKUP function to find certain values from different sheets and then execute our desired criteria to ease our work. So, we need to learn to use VLOOKUP with multiple criteria in different sheets in excel.


Download Practice Workbook

You can download the practice workbook from here.


4 Easy Methods to Use VLOOKUP with Multiple Criteria in Different Sheets

In this case, our goal is to use VLOOKUP with multiple criteria in different sheets in excel in 4 ways. To do that, we’ll use a sample dataset overview as an example in Excel to understand easily. In Column B we have Order No. and in columns C and D we have Item and Amount. If you follow the methods correctly, you should learn how to use VLOOKUP with multiple criteria in different sheets in excel yourself.

Dataset to Use VLOOKUP with Multiple Criteria in Different Sheets


1. Use of VLOOKUP Function on Each Sheet Individually

The VLOOKUP Function is mainly used to convert a numeric value to a particular formula for many purposes. In this case, we aim to use the function with multiple criteria in different sheets in excel. We will describe the steps of this method below.

Steps:

  • First, arrange a dataset like the below image.

Dataset to Use VLOOKUP with Multiple Criteria in Different Sheets

  • Then, in the C5 cell insert the following formula.
=VLOOKUP(B5, East!$C$5:$D$9, 2, FALSE)

Inserting Formula to Use VLOOKUP with Multiple Criteria in Different Sheets

  • After pressing the Enter button, you will get the result for the cell and then use the Fill Handle to apply the formula to all the desired cells.

Using Fill Handle to Use VLOOKUP with Multiple Criteria in Different Sheets

  • Afterward, you will see the result for that column.

  • Finally, if you follow the same steps, you will get the desired result.

Final Result to Use VLOOKUP with Multiple Criteria in Different Sheets

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


2. Using a Combination of VLOOKUP and IFERROR Functions

In this case, our goal is to use VLOOKUP with multiple criteria in different sheets in excel by a combination of the VLOOKUP and IFERROR Functions. The IFERROR Function is mainly used to apply certain series of conditions at the same time. Let’s describe the steps of this method below.

Steps:

  • Firstly, arrange a dataset like the below image.

  • Secondly, in the C5 cell insert the following formula.
=IFERROR(VLOOKUP($B5, East!$B$5:$D$9, COLUMNS($B$4:C$4), FALSE), IFERROR(VLOOKUP($B5, West!$B$5:$D$9, COLUMNS($B$4:C$4), FALSE), "Not found"))

Inserting Formula to Use VLOOKUP with Multiple Criteria in Different Sheets

  • Thirdly, if you press the Enter button, you will get the result for the cell and then use the Fill Handle to apply the formula to all the desired cells.

  • Fourthly, you will get the result for that column.

  • Fifthly, in the D5 cell insert the following formula.
=IFERROR(VLOOKUP($B5, East!$B$5:$D$9, COLUMNS($B$4:D$4), FALSE), IFERROR(VLOOKUP($B5, West!$B$5:$D$9, COLUMNS($B$4:D$4), FALSE), "Not found"))

Inserting Formula to Use VLOOKUP with Multiple Criteria in Different Sheets

  • Sixthly, if you press the Enter button, you will get the result for the cell and then use the Fill Handle to apply the formula to all the desired cells.

  • Lastly, you will get the desired result.

 🔎 How Does the Formula Work?

  • VLOOKUP($B5, West!$B$5:$D$9, COLUMNS($B$4:C$4), FALSE): In the first portion, we will find the desired cell ranges we want to use.
  • IFERROR(VLOOKUP($B5, East!$B$5:$D$9, COLUMNS($B$4:C$4), FALSE), IFERROR(VLOOKUP($B5, West!$B$5:$D$9, COLUMNS($B$4:C$4), FALSE), “Not found”)): This portion will apply the proper criteria in the formula.

Read More: How to Use VLOOKUP with Multiple Criteria in Different Columns


3. Using a Combination of VLOOKUP and Nested IFs Functions

Our goal is to use VLOOKUP with multiple criteria in different sheets in excel by combining the VLOOKUP Function and the Nested If function. The Nested If Function is mainly used to apply certain series of conditions at the same time. So, combining both functions can be very helpful in this case. Let’s describe the method with easy steps below in order.

Steps: 

  • To begin with, in the C5 cell insert the following formula.
=VLOOKUP($B5, IF(C$4="east", East_sales, IF(C$4="north", North_sales, IF(C$4="south", South_sales, IF(C$4="west", West_sales)))), 2, FALSE)

Inserting Formula to Use VLOOKUP with Multiple Criteria in Different Sheets

  • In addition, press the Enter button, and you will get the result for the cell, and then use the Fill Handle to apply the formula to all the desired cells.

  • Then, you will see the result for that column.

  • Finally, you will get the desired result if you apply the same steps to the desired cells.

 🔎 How Does the Formula Work?

  • IF(C$4=” west”, West_sales): In the first portion, it represents the selected sheets you want to use.
  • VLOOKUP($B5, IF(C$4=”east”, East_sales, IF(C$4=”north”, North_sales, IF(C$4=”south”, South_sales, IF(C$4=”west”, West_sales)))), 2, FALSE): This portion represents the proper conditions along the selected sheets.

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


4. Combining VLOOKUP and INDIRECT Functions

In this case, our goal is to use VLOOKUP with multiple criteria in different sheets in excel by the combination of VLOOKUP and INDIRECT Functions. The INDIRECT Function is mainly used to apply a certain condition at the same time. Let’s describe the steps of this method below.

Steps:

  • Firstly, in the C5 cell insert the following formula.
=VLOOKUP($B5, INDIRECT(C$4&"_sales"), 2, FALSE)

Inserting Formula to Use VLOOKUP with Multiple Criteria in Different Sheets

  • Secondly, if you press the Enter button, you will get the result for the cell and then use the Fill Handle to apply the formula to all the desired cells.

  • Thirdly, you will see the result for that column.

  • Fourthly, you will get the desired result.

 🔎 How Does the Formula Work?

  • INDIRECT(C$4&”_sales”): In the first portion, it represents the selected sheets.
  • VLOOKUP($B5, INDIRECT(C$4&”_sales”), 2, FALSE): This portion takes the value for the search and then finds the result according to the condition.

Read More: How to Apply VLOOKUP with Multiple Criteria Using the CHOOSE Function


How to Use VLOOKUP with Multiple Matches

At this point, we aim to use VLOOKUP with matches in excel by combining the VLOOKUP Function and the COUNTIF function. The COUNTIF Function is mainly used to apply certain series of conditions at the same time. So, combining both functions can be very helpful in this case. Let’s describe the method with easy steps below in order.

Steps:

  • At first, in the B5 cell insert the following formula.
=C5&COUNTIF(C5:$C$10,C5)

Inserting Formula to Use VLOOKUP with Multiple Criteria in Different Sheets

  • Second, click the Enter button to get the result for the cell, and then use the Fill Handle to apply the formula to all the desired cells.

  • Third, you will see the result for that column.

  • Forth, in the B13 cell insert the following formula.
=VLOOKUP(B$12&ROW($A$1:INDIRECT("A"&COUNTIF($C$5:$C$10,B$12))),$B$5:$E$10,4,FALSE)

Inserting Formula to Use VLOOKUP with Multiple Criteria in Different Sheets

  • Fifth, you will see the result for that column.

  • Last, you will get the desired result.

 🔎 How Does the Formula Work?

  • COUNTIF($C$5:$C$10, B$12): In the first portion, it represents the selected cells.
  • INDIRECT(“A”&COUNTIF($C$5:$C$10,B$12)): This portion applies the proper conditions.
  • VLOOKUP(B$12&ROW($A$1:INDIRECT(“A”&COUNTIF($C$5:$C$10, B$12))),$B$5:$E$10,4, FALSE): this portion takes the values in and finds out the result according to the criteria.

Things to Remember

  • Among all the methods, using the first method is the easiest to create and most efficient way to use it in all situations.
  • No matter which method you have used, just learn it well enough to understand its formula and final output.

Conclusion

Henceforth, follow the above-described methods. Thus, you will be able to use VLOOKUP with multiple criteria in different sheets in excel. Let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Zehad Rian Jim

Zehad Rian Jim

Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo