Zehad Rian Jim
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.
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.
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"))
- 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.
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)
- 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)
- 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)
- 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.
Read More: Excel VLOOKUP with Multiple Criteria in Column and Row
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.
