Buzz
Utilizing VLOOKUP with multiple conditions will help you search and reconcile data as accurately as possible based on the conditions you specify.
Tip: Add a helper column to concatenate 2 values together, then provide the concatenated value to the VLOOKUP function for a similar search.
Using VLOOKUP with Multiple Conditions
In the example above, utilizing VLOOKUP to look up employee department and group, but their first and last names are in 2 different columns, so there is no direct way to use both first and last names in the lookup. The solution is to use a helper column to combine first and last names, then use that auxiliary column to perform the lookup, the formula using VLOOKUP with multiple conditions at E3 is entered as follows:
=VLOOKUP(C3&D3,B6:F10,4,0)
In column B row 6, the helper column, the formula is used to concatenate first and last names using the concatenate function:
=C6&D6
Column C contains surnames and column D contains first names.
How does this formula work?
The auxiliary column helps concatenate values from the columns used as conditions. This column must be the first column of the table and acts as a 'key'. In the VLOOKUP function, the lookup value is also created by inputting similar conditions.
Setting Up Everything
To set up using the VLOOKUP function with multiple conditions, follow the 3 steps below:
1. Add an auxiliary column and concatenate values from the columns you want to use as conditions.
2. Set up the VLOOKUP formula referencing the table including the auxiliary column. Note that the auxiliary column must be the first column in the table.
3. For the lookup value, concatenate the same condition in the same order to match values in the auxiliary column.
Thus, Mytour has just guided you on how to use the VLOOKUP function with multiple conditions. VLOOKUP is highly applicable; you can use it effectively in accounting to assist your work. In addition to using VLOOKUP in accounting, many other Excel functions such as IF and DAY are also widely applied.
However, to proficiently use Excel functions, especially VLOOKUP, you need to practice with various VLOOKUP exercises shared on the internet or from experienced individuals. VLOOKUP exercises will be useful materials for you to enhance your knowledge of using VLOOKUP.
Hope this article will be helpful to you, providing you with more Excel formulas to support your work better.
