‘VLOOKUP’ is now so common that everyone who has ever been working with Excel has heard of it. Not necessarily because they know how to use it, but because some savvy Excel-user always talks about it at the office.
Advanced users have seen the use of adding more than one ‘criteria’ to what the function is looking for. So if they would be searching for a ‘Nate Harris’ in an employee database, they would not want to search the entire database. Instead, they would want to search for ‘Nate Harris’ but only in the ‘Sales-department’ of the company. This means that they would have two criteria for the search (‘Nate Harris’ and ‘Sales-department’).
By default, ‘VLOOKUP’ cannot help with a two criteria (or more) lookup. It is customary to change to the more flexible combination of functions, ‘INDEX’ and ‘MATCH’, and turning the formula into an array formula. However, this is not as easy and intuitive as using a simple ‘VLOOKUP’.
In this article, I will guide you to how you can actually create a ‘VLOOKUP’ with multiple criteria in Excel. It is an alteration of the typical way of using ‘VLOOKUP’ and is as intuitive as it is easy to use. But before you read on you should really know how to use ‘VLOOKUP’ the “normal” way. If you don’t, here’s an in-depth guide that will teach you.
The examples and screenshots below are from Excel 2016 for Windows, but the method is so universal that you can use this guide if you’re running Excel 2007 or newer.
If you want to tag along as we go, download the sample file here.
The general idea
The basics of using VLOOKUP in a multi-criteria scenario is that you will concatenate the different criteria so you can use them as a single lookup value. The concatenation will in this example be done with the ampersand (&). That means that before we can go any further in the lookup, we need to conjure up a helper column that joins the ‘Table_arrays’.
The helper column
In this example, and in the sample file, we need to make a separate column that conjoins the data from the ‘Employee’ column and the ‘Department’ column. Take a look at the screenshot below.
Insert a column after column B and name it whatever you want in cell C1.
Enter the following formula into cell C2 and copy it down to the rest of the rows in the data.
In short: The syntax of VLOOKUP
The things you need to put into a VLOOKUP formula to make it work (also called the syntax) is quite straightforward.
When you type in =VLOOKUP( in a cell, Excel tells you what input you need to your function.
The ‘lookup_value’ is what you are looking for, but not what you want as a result. This is often a thing you already know, for example, the name of the employee (column A) and his department (column B), while the thing you don’t know (in this case) is the salary (column C).
The ‘table_array’ is the area where you are looking for the data. If you were using a “normal” ‘VLOOKUP’ it would be column A through C. Because of the helper column the range we must use in this case is column C through D.
The ‘col_index_number’ tells which column you want to return the result from. There are two columns in our ‘table_array’ and we want to return the salary, which is located in column D (the second column of our ‘table_array’). This number should be 2 in our example.
The [range_lookup] is simply a ‘TRUE’ or ’FALSE’ that tells Excel if we want to use approximate or exact match for our lookup. In the article on ‘VLOOKUP’, I referred to before you can read all about this. In this example, we just write ‘FALSE’ for this parameter of the syntax.
Change the lookup value
The final step of creating a VLOOKUP with multiple criteria is to change the lookup value in the function.
The lookup value should be a concatenation of the two criteria you want to include in the VLOOKUP.
Let us put the VLOOKUP formula in F2, the first criteria in G2 and the second criteria in H2. The lookup value of the VLOOKUP should be: G2&H2.
The formula should now look like this:
You’re done already!
Using VLOOKUP for multi-criteria lookups is very easy (I told you!). Now you can change the criteria in cell G2 and H2 to test out your newly created multi-criteria lookup tool. If you want to take your lookup skills even further, you should read here!
Guest Blog Post By: