VLOOKUP’ is a common function that allows the Excel user to lookup and retrieve data. By default, ‘VLOOKUP’ cannot have more than one criteria to lookup. It is customary to change to the more flexible combination of functions, ‘INDEX’ and ‘MATCH’, etc. This post will show you how to create a VLOOKUP with multiple criteria in Excel.
The general formula for VLOOKUP
An explanation of VLOOKUP with multiple criteria
For you to use this function, you will need to have control over the source data. This is because you will need to create a helper column where you will be able to join multiple fields together. It is these fields that you will use as multiple criteria in VLOOKP.
How VLOOKUP with multiple criteria formula works
To fully understand how this formula works, we shall have to use examples.
Figure 1: Using VLOOKUP with multiple criteria
In figure 1 above, we have names of staff in a certain firm and their respective departments. We also have the group that each individual belongs to. You should also notice the helper column, which will concatenate the first and last name of the individuals. For our case, the helper column is column C.
In our example above, our objective is to look up the department of employee and his group using VLOOKUP. To do this, we shall need to match the first and last name.
For us to match the first and second name, we shall need to use the concatenation function. The syntax for the concatenation for this case is as below:
Figure 2: Using VLOOKUP with multiple criteria
When you press “Enter”, the function will combine the first and second name and bring them together in column C.
After that, hold and drag the formula across the entire column C. this will yield the results as shown below:
Figure 3: Using VLOOKUP with multiple criteria
After this, we now need to use the VLOOKUP function to find the VLOOKUP
Say we want to look up John Jones, we proceed as follows:
This function will pull up the department for John Jones as “Engineering.”
Note that when we use 1, we get an approximate value, but with 0, we are able to get the exact match.
Let us look at another example to further understand how the VLOOKUP for multiple criteria works.
Figure 4: Using VLOOKUP with multiple criteria
In this example, we want to use the VLOOKUP to find the marks that John Jones got in Chemistry. We proceed as follows:
Create the helper column
Figure 5: Using VLOOKUP with multiple criteria
Then, concatenate to get the names in the helper column. This is done using the CONCATENATE function as shown:
Then copy down the formula to get the result for other cells.
Figure 6: Using VLOOKUP with multiple criteria
After concatenation, now we need to use the VLOOKUP function to find the marks that John Jones got in chemistry. Proceed as follows:
Note that instead of writing “JohnJones”, we have used the cell reference.
Figure 7: Using VLOOKUP with multiple criteria
Then press “Enter” to get the marks for John Jones.
For you to get an exact match, do not forget to specify it using zero (0).
And for our example above, the answer is 78.
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.