# How to Use VLOOKUP with Multiple Criteria in Different Sheets

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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. ### 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. • Then, in the C5 cell insert the following formula.
`=VLOOKUP(B5, East!\$C\$5:\$D\$9, 2, FALSE)` • 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. • Afterward, you will see the result for that column. • Finally, if you follow the same steps, you will get the desired result. ### 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"))` • 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"))` • 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.

### 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)` • 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.

### 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)` • 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.

## 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)` • 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)` • 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 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 Advanced Excel Exercises with Solutions PDF  