Excel Lookup Function lookup multiple criteria not in first column
In this tutorial, I will explain how to use Excel Lookup Function to lookup multiple criteria while the lookup value is not in first column of lookup range. The reason for writing this article is that Vlookup can only lookup one criteria and lookup value must be in the first column of lookup table.
You can also use SUMPRODUCT Function to achieve the same result in the below article.
Excel SumProduct lookup multiple criteria
Syntax of Lookup Function
LOOKUP(lookup_value, lookup_range, result_range)
| lookup_value | Required. A value that LOOKUP searches for in the first vector |
| lookup_range | Required. A range that contains only one row or one column |
| result_range | Optional. A range that contains only one row or column. |
If the LOOKUP function can not find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value. We will make use of this property in lookup multiple criteria.
Example of Lookup Function – lookup multiple criteria
In this example, we have two worksheets:
1) source – the source table being looked up
2) lookup – the lookup table in which you want to lookup salary from source
First Name and Bbirthdate are keys to identify an unique person.
Now apply the lookup formula as below in column C. Column D is to show the actual formula of column C.
Explanation of Formula – lookup multiple criteria
Let’s take a look at the formula at D2
=LOOKUP(2,1/((source!$B$2:$B$11=lookup!A2)*(source!$C$2:$C$11=lookup!B2)),{10000;20000;30000;...})
| Parameter name | Parameter value |
| lookup_value | 2 |
| lookup_range | 1/((source!$B$2:$B$11=lookup!A2)*(source!$C$2:$C$11=lookup!B2)) |
| result_range | source!$A$2:$A$11 |
Below is the mechanism of the formula.
=LOOKUP(2,1/((source!$B$2:$B$11=lookup!A2)*(source!$C$2:$C$11=lookup!B2)),source!$A$2:$A$11)
=LOOKUP(2,1/({Apple;Ben;Ben;Cat;...="Cat"}*{29221;29222;29223;29224;...=29224}),{10000;20000;30000;40000;...})
=LOOKUP(2,1/({FALSE;FALSE;FALSE;TRUE;...}*{FALSE;FALSE;FALSE;TRUE;...}),{10000;20000;30000;40000;...})
=LOOKUP(2,1/{0;0;0;1;...},{10000;20000;30000;40000;...})
=LOOKUP(2,{#DIV/0;#DIV/0;#DIV/0;1;...},{10000;20000;30000;40000;...})
=40000
In the second parameter
1/{0;0;0;1;…}
The reason for dividing an array by 1 is that we want to turn all “0” to non numerical value (i.e. error)
{#DIV/0;#DIV/0;#DIV/0;1;...}
Afterwards, we apply LOOKUP formula and use “2” as the lookup_value parameter, where “2” is a constant that can be applied to other formula. When we lookup a value (2) which cannot be found in the array (a combination of 1 and #DIV/0), the Function will match the largest value (1) in the lookup_range (1 and #DIV/0) that is less than or equal to the value (2). In fact, you can also use “1” as the lookup_value parameter assume that value 1 can be found in the array, the Function will return the last matched value as well. As a normal practice, people tend to use “2” as parameter.
Outbound References
http://blogs.office.com/2012/04/26/using-multiple-criteria-in-excel-lookup-formulas/