Using Excel’s Multiple Criteria In VLOOKUP Function

Using Excel’s Multiple Criteria In VLOOKUP Function

Microsoft Excel’s VLOOKUP function is a popular feature amongst office personnel and data processor positions. Those users who already have an advance understanding of this function have utilized it by putting an additional criterion for search purposes.

The default characteristic of the VLOOKUP function is that it cannot have a lookup with two or more criteria. Most people use it with a combination of other functions such as Match and Index to deliver desired results.

In the following example, steps on customizing a VLOOKUP function with multiple criteria are discussed. The demonstrations are done using Microsoft Excel 2016 (Windows) but the general concept can be used universally across other versions of the program.

Main Concept

To do this multiple criteria VLOOKUP, all the desired criteria must be compressed into the main function. Using the ampersand (&) symbol, we can input all the criteria within the syntax. In line with this, we will need to join the ‘Table_arrays’ by creating a helper column.

Helper Column

For this demonstration, we will create a distinct column to combine the data in the Employee and Department columns. Insert a new column after B and name it as defined by your purpose in cell C1. Input =A2&B2 into cell C2 and copy this to all the succeeding cells.

VLOOKUP Syntax 

When inputting the =VLOOKUP, Excel will display the needed values you will need to supply for the function to perform accurately. These are: ‘lookup_value’, ‘table_array’, ‘col_index_number’ and [range_lookup].

The Lookup Value

The last detail in this multi-criteria VLOOKUP is to edit the function’s lookup value. It must be the combined criteria you would like to incorporate in the function. For this example, we will put the formula in Cell F2, the first and second criteria in cells G2 and H2 respectively. The resulting input should now be G2&H2

That’s it!

The above steps are the plain gist of the process. You can now use this method for yourself and try it out on different purposes.