Excel Vlookup multiple criteria

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.

Excel Vlookup multiple criteria

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.

=A2&B2

 

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.

Excel Vlookup multiple criteria

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.

Excel Vlookup multiple criteria

The formula should now look like this:

Excel Vlookup multiple criteria

 

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:

Kasper Langmann from Spreadsheeto.com

Excel Vlookup multiple criteria

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

INDEX Function with Data Validation To make the INDEX function in Excel interactive we can add a Data Validation drop down list. By referencing the second argument in the INDEX function - the Row Number - to a data validation list, will allow a user to choose the nth position of a list and return their respectiv...
Vlookup in an Excel Table What does it do? Searches for a value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array. Formula breakdown: =VLOOKUP(lookup_value, table_array, col_index_num, ) What it means: =VLOOKUP(thi...
Get the Average of Values with Excel’s AVERA... What does it do? Gives you the average of a group of values Formula breakdown: =AVERAGE(number1, number2...) What it means: =AVERAGE(the numbers you want to average)   There are times when you have to get the average of your values in your Excel work...
Cleaning Data with Excel’s CLEAN Formula   What does it do? Removes all nonprintable characters from text Formula breakdown: =CLEAN(text) What it means: =CLEAN(this dirty text cell) There are times when imported text from other applications contain characters that are unprintable. The CLEAN...