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

Excel Vlookup multiple criteria

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Cleaning Data with Excel’s CLEAN Formula  What does it do?Removes all nonprintable characters from textFormula 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...
Return the Last Value in a Column with the Offset ... What does it do?It returns a reference to a range, from a starting point to a specified number of rows, columns, height and width of cellsFormula breakdown:=OFFSET(reference, rows, columns, , )What it means:=OFFSET(start in this cell, go up/down a number of ro...
CountA Formula in Excel What does it do?Counts the number of cells that are non-blank/non-empty (including empty text "")Formula breakdown:=COUNTA(value1, , ...)What it means:=COUNTA(value or range of cells to check, , ...)Do you have a scenario where you want to count the nu...
CountIf Formula in Excel What does it do?Counts the number of cells that matches your specified conditionFormula breakdown:=COUNTIF(range, criteria)What it means:=COUNTIF(range of cells to check, condition to check against)Do you have a scenario where you want to count the num...