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_value	A Range or array that LOOKUP searches for
lookup_range	A lookup Range or array that contain only one row or one column, must be sorted in ascending order
result_range	Optional. 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.

Formula	Result	Explanation
=LOOKUP(B2,source!C2:C11,source!A2:A11)	40000	Look up Salary where Birthdate is 4/1/1980
=LOOKUP(“Cat”,source!B2:B11,source!A2:A11)	60000	Look 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.

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.

References – ASCII value of text

Note the conversion of Dec and Char

Dec	Hex	Oct	Char	Description
0	0	000		null
1	1	001		start of heading
2	2	002		start of text
3	3	003		end of text
4	4	004		end of transmission
5	5	005		enquiry
6	6	006		acknowledge
7	7	007		bell
8	8	010		backspace
9	9	011		horizontal tab
10	A	012		new line
11	B	013		vertical tab
12	C	014		new page
13	D	015		carriage return
14	E	016		shift out
15	F	017		shift in
16	10	020		data link escape
17	11	021		device control 1
18	12	022		device control 2
19	13	023		device control 3
20	14	024		device control 4
21	15	025		negative acknowledge
22	16	026		synchronous idle
23	17	027		end of trans. block
24	18	030		cancel
25	19	031		end of medium
26	1A	032		substitute
27	1B	033		escape
28	1C	034		file separator
29	1D	035		group separator
30	1E	036		record separator
31	1F	037		unit separator
32	20	040		space
33	21	041	!
34	22	042	“
35	23	043	#
36	24	044	$
37	25	045	%
38	26	046	&
39	27	047	‘
40	28	050	(
41	29	051	)
42	2A	052	*
43	2B	053	+
44	2C	054	,
45	2D	055	–
46	2E	056	.
47	2F	057	/
48	30	060	0
49	31	061	1
50	32	062	2
51	33	063	3
52	34	064	4
53	35	065	5
54	36	066	6
55	37	067	7
56	38	070	8
57	39	071	9
58	3A	072	:
59	3B	073	;
60	3C	074	<
61	3D	075	=
62	3E	076	>
63	3F	077	?
64	40	100	@
65	41	101	A
66	42	102	B
67	43	103	C
68	44	104	D
69	45	105	E
70	46	106	F
71	47	107	G
72	48	110	H
73	49	111	I
74	4A	112	J
75	4B	113	K
76	4C	114	L
77	4D	115	M
78	4E	116	N
79	4F	117	O
80	50	120	P
81	51	121	Q
82	52	122	R
83	53	123	S
84	54	124	T
85	55	125	U
86	56	126	V
87	57	127	W
88	58	130	X
89	59	131	Y
90	5A	132	Z
91	5B	133	[
92	5C	134	\
93	5D	135	]
94	5E	136	^
95	5F	137	_
96	60	140	`
97	61	141	a
98	62	142	b
99	63	143	c
100	64	144	d
101	65	145	e
102	66	146	f
103	67	147	g
104	68	150	h
105	69	151	i
106	6A	152	j
107	6B	153	k
108	6C	154	l
109	6D	155	m
110	6E	156	n
111	6F	157	o
112	70	160	p
113	71	161	q
114	72	162	r
115	73	163	s
116	74	164	t
117	75	165	u
118	76	166	v
119	77	167	w
120	78	170	x
121	79	171	y
122	7A	172	z
123	7B	173	{
124	7C	174	\|
125	7D	175	}
126	7E	176	~
127	7F	177	DEL

Wyman W

Compensation Survey Manager@Mercer

Wyman is human resources professional, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

You may drop me a message if you are interested in HR consultancy services from Mercer.