Excel Lookup Function lookup multiple criteria

This Excel tutorial explains how to use Excel Lookup Function and how to look up multiple criteria.

You may also want to read:

Excel Vlookup second matched value or specific occurence

Excel Lookup Function lookup multiple criteria not in the first column

Excel lookup partial text using Vlookup with Wildcard

Excel Lookup Function – lookup multiple criteria

Excel Lookup Function is  to look up a value in a Range (or array) and return the corresponding result in another Range (or array). Note that “Range” is also known as “Vector” if you read other tutorial.

Differences between Lookup and Vlookup

First of all, lets recap the syntax of Vlookup

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Lookup Function behaves similarly as Vlookup if the last parameter [range_lookup] of vlookup is set to TRUE.

TRUE means to look for similar match, FALSE means to look for exact match. It is not helpful to look for similar match in 99% situations because it may not return the exact result we want. In Lookup Function, we cannot choose to look for exact match or similar match, we can only look for similar match.

In order to make an apple to apple comparison, I will talk about how Lookup behaves differently against Vlookup with FALSE [range_lookup] below.

1 – For Lookup Function, lookup Range must be in ascending order. Vlookup (last Function parameter =FALSE) does not have such requirement.

2 – Lookup Function can look up value not in the first column of lookup Range, because you can specify the lookup Range anywhere instead of specifying the Nth column in lookup table

3 – If there are more than one matched results, Vlookup (last Function parameter =FALSE) returns the first matched value, while Lookup returns the last matched value

3 – If Lookup Function cannot find an exact match, it returns the largest value in the lookup Range that is less than or equal to the lookup value (behave the same way as Vlookup when the last parameter is set to TRUE). While you can easily identify which is larger for numeric value (for example, 1000 > 10).

For text like alphabets or symbols, the comparison is based on ASCII value of the text (for example, a> A >?> 1), read the references at the bottom of the post for ASCII value and text conversion.

Therefore even if there is no match, Lookup Function may still return a value, while Vlookup returns #N/A.

Syntax of Lookup Function

LOOKUP(lookup_value, lookup_range, result_range)
lookup_valueA Range or array that LOOKUP searches for
lookup_rangeA lookup Range or array that contain only one row or one column, must be sorted in ascending order
result_rangeOptional. A range that contains only one row or column.

Click here to see the detailed explanation.

Example of Lookup Function

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 Birthdate are keys to identify an unique person.

lookup_00

FormulaResultExplanation
=LOOKUP(B2,source!C2:C11,source!A2:A11)40000Look up Salary where Birthdate is 4/1/1980
=LOOKUP(“Cat”,source!B2:B11,source!A2:A11)60000Look up Salary where First Name is Cat. Because there are more than 1 Cat, result of last Cat is returned.
=LOOKUP(“C”,source!B2:B11,source!A2:A11)30000“C” cannot be found in lookup Range, so the Function would return the largest value less than “C”, which is the last “Ben”
=LOOKUP(“A”,source!B2:B11,source!A2:A11)#N/A“A” cannot be found in lookup Range, so the Function would return the largest value less than “A”, but the smallest in the lookup Range is “Apple”, so nothing is smaller than “A”

Advanced use of Lookup Function – lookup multiple criteria

Apply the lookup formula as below in column C. Column D is to show the actual formula of column C.

lookup_06

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 nameParameter value
lookup_value2
lookup_range1/((source!$B$2:$B$11=lookup!A2)*(source!$C$2:$C$11=lookup!B2))
result_rangesource!$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.

References – ASCII value of text

Note the conversion of Dec and Char

DecHexOctCharDescription
00000null
11001start of heading
22002start of text
33003end of text
44004end of transmission
55005enquiry
66006acknowledge
77007bell
88010backspace
99011horizontal tab
10A012new line
11B013vertical tab
12C014new page
13D015carriage return
14E016shift out
15F017shift in
1610020data link escape
1711021device control 1
1812022device control 2
1913023device control 3
2014024device control 4
2115025negative acknowledge
2216026synchronous idle
2317027end of trans. block
2418030cancel
2519031end of medium
261A032substitute
271B033escape
281C034file separator
291D035group separator
301E036record separator
311F037unit separator
3220040space
3321041!
3422042
3523043#
3624044$
3725045%
3826046&
3927047
4028050(
4129051)
422A052*
432B053+
442C054,
452D055
462E056.
472F057/
48300600
49310611
50320622
51330633
52340644
53350655
54360666
55370677
56380708
57390719
583A072:
593B073;
603C074<
613D075=
623E076>
633F077?
6440100@
6541101A
6642102B
6743103C
6844104D
6945105E
7046106F
7147107G
7248110H
7349111I
744A112J
754B113K
764C114L
774D115M
784E116N
794F117O
8050120P
8151121Q
8252122R
8353123S
8454124T
8555125U
8656126V
8757127W
8858130X
8959131Y
905A132Z
915B133[
925C134\
935D135]
945E136^
955F137_
9660140`
9761141a
9862142b
9963143c
10064144d
10165145e
10266146f
10367147g
10468150h
10569151i
1066A152j
1076B153k
1086C154l
1096D155m
1106E156n
1116F157o
11270160p
11371161q
11472162r
11573163s
11674164t
11775165u
11876166v
11977167w
12078170x
12179171y
1227A172z
1237B173{
1247C174|
1257D175}
1267E176~
1277F177DEL