Buzz
As we know, the Vlookup function is widely used for precise value search, a fundamental skill for anyone learning Excel. With the Vlookup function with two conditions, you can enhance your proficiency in using Vlookup and Excel, as demonstrated through effective examples in this article.
Discussing the Vlookup function with two conditions, you can easily search for a specific value within a range with more advanced requirements spanning multiple rows and columns. Follow our guide below to learn how to utilize the Vlookup function with two conditions.
Guide to Using Vlookup Function with Two Conditions
1. Creating an Additional Column for Vlookup Function with Two Conditions
To calculate the specific output of a product in each shift, how would we proceed? By applying the Vlookup function with two conditions, let's try adding an extra column to simplify the calculation process.
- Essentially, using an additional column in the Vlookup function with two conditions transforms it into a Vlookup function with one condition. With this approach, you can search for multiple different conditions.
Step 1: Now, let's utilize the Vlookup function with two conditions for calculations, accompanied by creating an auxiliary column. In the example below, Mytour associates Column A with values from Column B - Product Code and Column C - Shift.
Enter the formula into Column A: =B6&C6 then press Enter to execute the command.
Step 2: Display the result as shown below, combining the two columns of product and shift, then apply it to the rows below.
Step 3: To search for the quantity of a product in a specific shift, create a query table next to it. Here, we must input the Product Code and shift; the quantity section is where we input the formula.
Step 4: Here, input the formula =Vlookup(G6&G7);$A$6:$D$10;4;0) as shown below, applying it correctly with the specified rows and columns in the example.
Explanation
- =Vlookup: this is the function we use for searching and applying
- G6&G7: Values used to query the table and provide corresponding results
- ;$A$6:$D$10: is the search range from Column A6 to D10 (Note to press F4 to display $ symbol)
- 4;0: 4 here signifies returning the value in the fourth column, where 0 corresponds to the logical True or False value.
- If 0 means False, the function will return an absolute value result, while 1 is True, resulting in a relative value.
Step 5: The result will show a quantity of 1000 corresponding to the product code SA in Shift 1. You can cross-reference by entering different product codes and shifts for comparison.
2. Vlookup Function with Two Conditions Using Array Formula
Array formula is an advanced calculation method in Excel that not everyone pays attention to due to its complexity. However, you can apply it with the Vlookup function with two or more conditions.
Step 1: Same spreadsheet, but this time we will eliminate the auxiliary column and input the formula directly into the quantity section. Enter the following formula:
=VLOOKUP(G6&G7;CHOOSE({1\2};(B6:B10)&(C6:C10);D6:D10);2;0)
- With G6&G7 as the search condition, similar to method 1, combining two columns into one auxiliary column.
- Choose is a function for finding a value in a string, and here, when combined, it will search for a value satisfying the condition.
- 2 is the value returned in the quantity result column, and 0 remains the absolute False value mentioned earlier.
Step 2: The displayed result will appear as shown below after entering the formula and pressing Ctrl + Shift + Enter to calculate the array formula.
The advantage of using the Vlookup function with two conditions and array formula is that it doesn't increase data volume, and the search doesn't require an auxiliary column. However, the disadvantage is that it's challenging to write and can be confusing for most users.
Here are different ways to use the Vlookup function with two conditions for your reference. This is an advanced topic for those already using Vlookup. Besides using the Vlookup function with two conditions, an advanced article on combining Vlookup and Left function is worth exploring. Discover the benefits of combining Vlookup and Left function on your own.
