In this Excel VLOOKUP Tutorial, you learn how to use the VLOOKUP function in worksheet formulas.
This Excel VLOOKUP Tutorial is currently under development. Subscribe to the Power Spreadsheets Newsletter to get future updates to this Excel VLOOKUP Tutorial.
Use the following Table of Contents to navigate to the Section you're interested in.
Table of Contents
Excel VLOOKUP Exact Match
Worksheet Formula to Obtain an Exact Match with VLOOKUP
To obtain an exact match with the VLOOKUP function, use the following structure/template in the applicable worksheet formula.
=VLOOKUP(ValueYouLookFor,CellRangeWhereYouLookIn,NumberOfColumnWithValueToReturn,FALSE)
Process to Obtain an Exact Match with VLOOKUP
 Specify the following when working with the VLOOKUP function:
 The value you look for.
 The cell range where you look in.
 The number of the column (in the cell range where you look in) containing the value to return.
Main Constructs to Obtain an Exact Match with VLOOKUP
(1) VLOOKUP function.
When carrying out an exact match VLOOKUP, the VLOOKUP function does the following:
 Searches for a value (the lookup value) vertically/down the first/leftmost column in a cell range (a table).
 Returns a value on the same row (as the lookup value) but a different column (you specify).
The VLOOKUP function is used to find things in a cell range (for example, a table):
 By row.
 When the cell range (table) is organized vertically (entries are organized by rows).
(2) lookup_value argument.
The:
 First argument of the VLOOKUP function.
 The value you look up or search for (the lookup value) in the first/leftmost column of the table.
(3) table_array argument.
The:
 Second argument of the VLOOKUP function.
 Cell range where you look in (the table with the applicable data).
The first/leftmost column of the cell range specified as table_array must contain the lookup value. In other words: The VLOOKUP function searches for the lookup value in the first/leftmost column of the table (specified as table_array argument).
The cell range specified as table_array argument must (also) contain the column containing the value to return. You specify the number of this column (containing the value to return) in the cell range specified as table_array argument with the col_index_num argument.
(4) col_index_num argument.
The:
 Third argument of the VLOOKUP function.
 Number of the column (in the table) containing the value to return.
 The first column in the table is column 1.
 The second column in the table is column 2.
 …
 The nth column in the table is column n.
(5) range_lookup argument.
The fourth argument of the VLOOKUP function. Optional argument.
Specifies whether you want an approximate or exact match.
If you omit the range_lookup argument, the VLOOKUP function:
 Returns an approximate match; but
 (Usually) Returns an exact match if:
 An exact match exists; and
 The table is sorted in ascending order.
Cues to Obtain an Exact Match with VLOOKUP
 The column containing the lookup value must (as a general rule) be:
 The first/leftmost column in the table.
 To the left of the column containing the value to return.
 The VLOOKUP function is case insensitive.
 As a general rule, if the first/leftmost column in the table contains duplicate values (and you look up one of those duplicate values), the VLOOKUP function works with the first entry matching the lookup value.
 If the VLOOKUP function fails to find an exact match for the lookup value in the first/leftmost column of the table, it returns the #N/A error.
 To obtain an exact match, set the range_lookup argument to FALSE.
Worksheet Formula Examples to Obtain an Exact Match with VLOOKUP
The worksheet formulas below obtain an exact match with VLOOKUP by specifying VLOOKUP's arguments as follows:
 lookup_value: The value stored in cell K9 ($K$9).
 table_array: The table in cells A9 to G28 ($A$9:$G$28).
 col_index_num: The column number stored in column I (I10, I11, I12, I13, I14, I15).
 range_lookup: FALSE.
Worksheet Formula  
1 
'Source: https://powerspreadsheets.com/ 
2 
'Source: https://powerspreadsheets.com/ 
3 
'Source: https://powerspreadsheets.com/ 
4 
'Source: https://powerspreadsheets.com/ 
5 
'Source: https://powerspreadsheets.com/ 
6 
'Source: https://powerspreadsheets.com/ 
Effects of Worksheet Formula Examples to Obtain an Exact Match with VLOOKUP
The image below illustrates the effects of working with the worksheet formula examples.
In this example:
 Cells A8 to G28 contain a table with the following characteristics:
 7 columns (Salesperson, Date, Customer, Product, City, Units, Unit Price).
 1 header row (row 8).
 20 entries (rows 9 to 28).
 Cells I8 to L15 contain a table with the following characteristics:
 4 columns (Column, Header, Value, Formula).
 Cell K9 contains the lookup value.
 Cells I10 to I15 contain numbers. These numbers match the column numbers (see cells A6 to G6) of the first table in the worksheet (the table VLOOKUP works with).
 Cells K10 to K15 contain the example worksheet formulas (working with the VLOOKUP function).
 Cells L10 to L15 display the example worksheet formulas.
The example worksheet formulas (in cells K10 to K15) do the following:
 Search for the lookup value (Salesperson 8) in the first/leftmost column (column A) of the table. The lookup value is found in cell A27.
 Return the values in the same row (row 27 of the worksheet) but in the columns specified in cells I10 to I15 as follows:
 Cell K10: Value in the second column of the table (03Jun14 in cell B27).
 Cell K11: Value in the third column of the table (Customer 44 in cell C27).
 Cell K12: Value in the fourth column of the table (Product 8 in cell D27).
 Cell K13: Value in the fifth column of the table (City 3 in cell E27).
 Cell K14: Value in the sixth column of the table (45.00 in cell F27).
 Cell K15: Value in the seventh column of the table (399.99 in cell G27).
Excel VLOOKUP Approximate Match (Range VLOOKUP)
Worksheet Formula to Obtain an Approximate Match with VLOOKUP (Carry Out a Range VLOOKUP)
To obtain an approximate match with the VLOOKUP function (carry out a range VLOOKUP), use the following structure/template in the applicable worksheet formula.
=VLOOKUP(ValueYouLookFor,CellRangeWhereYouLookIn,NumberOfColumnWithValueToReturn,TRUE)
Process to Obtain an Approximate Match with VLOOKUP (Carry Out a Range VLOOKUP)
 Specify the following when working with the VLOOKUP function:
 The value you look for.
 The cell range where you look in.
 The number of the column (in the cell range where you look in) containing the value to return.
Main Constructs to Obtain an Approximate Match with VLOOKUP (Carry Out a Range VLOOKUP)
(1) VLOOKUP function.
When carrying out an approximate match VLOOKUP (a range VLOOKUP), the VLOOKUP function does the following:
 Searches for a value vertically/down the first/leftmost column in a cell range (a table). The searched value meets 2 conditions:
 The searched value is less than or equal to the specified value (the lookup value).
 The searched value is the closest value to the lookup value (of those values that are less than or equal to the lookup value).
 Searches vertically/down the applicable column. Strictly speaking, Excel does this using a binary search technique.
 Stops its search once it finds a value that is greater than the lookup value.
 Goes back/up 1 row. This is the row immediately above that where it found the first value greater than the lookup value.
 Returns a value on the same row (as the row immediately above that where it found the first value greater than the lookup value) but a different column (you specify).
The VLOOKUP function is used to find things in a cell range (for example, a table):
 By row.
 When the cell range (table) is organized vertically (entries are organized by rows).
(2) lookup_value argument.
The:
 First argument of the VLOOKUP function.
 The value you use as a basis for the lookup or search in the first/leftmost column of the table (the lookup value).
(3) table_array argument.
The:
 Second argument of the VLOOKUP function.
 Cell range where you look in (the table with the applicable data).
The VLOOKUP function searches for the applicable value (the closest value, of those that are less than or equal, to the lookup value) in the first/leftmost column of the table (specified as table_array argument).
The cell range specified as table_array argument must contain the column containing the value to return. You specify the number of this column (containing the value to return) in the cell range specified as table_array argument with the col_index_num argument.
(4) col_index_num argument.
The:
 Third argument of the VLOOKUP function.
 Number of the column (in the table) containing the value to return.
 The first column in the table is column 1.
 The second column in the table is column 2.
 …
 The nth column in the table is column n.
(5) range_lookup argument.
The fourth argument of the VLOOKUP function. Optional argument.
Specifies whether you want an approximate or exact match. An approximate match lookup is also known as a range lookup (you lookup a value inside a range of values).
If you omit the range_lookup argument, the VLOOKUP function:
 Returns an approximate match; but
 (Usually) Returns an exact match if:
 An exact match exists; and
 The table is sorted in ascending order.
If you want an approximate match (set the range_lookup argument to TRUE or omit the range_lookup argument) the first column (in the table) must be sorted in ascending order.
Cues to Obtain an Approximate Match with VLOOKUP (Carry Out a Range VLOOKUP)
 The column you use as a basis for the lookup or search must (as a general rule) be:
 The first/leftmost column in the table.
 To the left of the column containing the value to return.
 Be sorted in ascending order.
 If the lookup value is greater than the last value in the first/leftmost column of the table, the VLOOKUP function works with that last value in the column.
 The VLOOKUP function is case insensitive.
 To obtain an approximate match (carry out a range VLOOKUP) set the range_lookup argument to TRUE.
Worksheet Formula Examples to Obtain an Approximate Match with VLOOKUP (Carry Out a Range VLOOKUP)
The worksheet formulas below obtain an approximate match with VLOOKUP (carry out a range VLOOKUP) by specifying VLOOKUP's arguments as follows:
 lookup_value: The value stored in cell column F (F9, F10, F11, F12, F13, F14, F15, F16, F17, F18, F19, F20, F21, F22, F23, F24, F25, F26, F27, F28).
 table_array: The table in cells J9 to K17 ($J$9:$K$17).
 col_index_num: 2.
 range_lookup: TRUE.
Worksheet Formula  
1 
'Source: https://powerspreadsheets.com/ 
2 
'Source: https://powerspreadsheets.com/ 
3 
'Source: https://powerspreadsheets.com/ 
4 
'Source: https://powerspreadsheets.com/ 
5 
'Source: https://powerspreadsheets.com/ 
6 
'Source: https://powerspreadsheets.com/ 
7 
'Source: https://powerspreadsheets.com/ 
8 
'Source: https://powerspreadsheets.com/ 
9 
'Source: https://powerspreadsheets.com/ 
10 
'Source: https://powerspreadsheets.com/ 
11 
'Source: https://powerspreadsheets.com/ 
12 
'Source: https://powerspreadsheets.com/ 
13 
'Source: https://powerspreadsheets.com/ 
14 
'Source: https://powerspreadsheets.com/ 
15 
'Source: https://powerspreadsheets.com/ 
16 
'Source: https://powerspreadsheets.com/ 
17 
'Source: https://powerspreadsheets.com/ 
18 
'Source: https://powerspreadsheets.com/ 
19 
'Source: https://powerspreadsheets.com/ 
20 
'Source: https://powerspreadsheets.com/ 
Effects of Worksheet Formula Examples to Obtain an Approximate Match with VLOOKUP (Carry Out a Range VLOOKUP)
The image below illustrates the effects of working with the worksheet formula examples.
In this example:
 Cells J8 to K17 contain a table with the following characteristics:
 2 columns (Total Sales are Greater Than or Equal To, Commission).
 1 header row (row 8).
 9 entries (rows 9 to 17).
 Cells A8 to H28 contain a table with the following characteristics:
 9 columns (Salesperson, Product, City, Units, Unit Price, Total Sales, Commission, Formula).
 1 header row (row 8).
 20 entries (rows 9 to 28).
 Column F (Total Sales, cells F9 to F28) contains the lookup values.
 Column G (Commission, cells G9 to G28) contain the example worksheet formulas (working with the VLOOKUP function).
 Column H (Formula, cells H9 to H28) displays the example worksheet formulas.
The example worksheet formulas (in column G) do the following:
 Search for a value (the closest value, of those that are less than or equal, to the lookup value specified in column F) in the first/leftmost column (column J) of the table.
 Return the value in the same row but in the second column of the table (column K).
Excel VLOOKUP Multiple Criteria (with VLOOKUP Function and Helper Column)
Worksheet Formula to Carry Out a VLOOKUP with Multiple Criteria (with the VLOOKUP Function and a Helper Column)
To carry out a VLOOKUP with multiple criteria (with the VLOOKUP function and a helper column):
 Use the following structure/template in the applicable worksheet formula.
=VLOOKUP(ValueYouLookFor1&Separator&ValueYouLookFor2&Separator&ValueYouLookFor3&Separator&…&ValueYouLookFor#,CellRangeWhereYouLookIn,NumberOfColumnWithValueToReturn,FALSE)
 Use the following worksheet formula structure/template to create a helper column in the cell range where you look in.
=ReferenceToColumnWithValueYouLookFor1&Separator&ReferenceToColumnWithValueYouLookFor2&Separator&ReferenceToColumnWithValueYouLookFor3&Separator&…&ReferenceToColumnWithValueYouLookFor#
Process to Carry Out a VLOOKUP with Multiple Criteria (with the VLOOKUP Function and a Helper Column)
 Add a helper column to the cell range where you look in (the table with the applicable data).
 Use the helper column to concatenate the data in the applicable columns (the columns containing the multiple criteria).
 Specify the following when working with the VLOOKUP function:
 The value you look for, built by concatenating the multiple criteria.
 The cell range where you look in.
 The number of the column (in the cell range where you look in) containing the value to return.
Main Constructs to Carry Out a VLOOKUP with Multiple Criteria (with the VLOOKUP Function and a Helper Column)
(1) Helper column.
Extra column added to a data set with the (main) objective of simplifying a complex task/calculation.
(2) Ampersand (&) calculation operator.
Concatenates strings to create a new string.
(3) VLOOKUP function.
When carrying out a VLOOKUP with multiple criteria (with the VLOOKUP function and a helper column), the VLOOKUP function does the following:
 Searches for a value (the lookup value, which concatenates the multiple criteria) vertically/down the first/leftmost column (the helper column) in a cell range (a table).
 Returns a value on the same row (as the lookup value) but a different column (you specify).
The VLOOKUP function is used to find things in a cell range (for example, a table):
 By row.
 When the cell range (table) is organized vertically (entries are organized by rows).
(4) lookup_value argument.
The:
 First argument of the VLOOKUP function.
 The value you look up or search for (the lookup value, which concatenates the multiple criteria) in the table's helper column.
(5) table_array argument.
The:
 Second argument of the VLOOKUP function.
 Cell range where you look in (the table with the applicable data).
The first/leftmost column of the cell range specified as table_array must contain the lookup value. In other words: The VLOOKUP function searches for the lookup value in the first/leftmost column of the table (specified as table_array argument).
Therefore, to carry out a VLOOKUP with multiple criteria (with the VLOOKUP function and a helper column), the helper column must be the first/leftmost column of the table specified as table_array argument.
The cell range specified as table_array argument must (also) contain the column containing the value to return. You specify the number of this column (containing the value to return) in the cell range specified as table_array argument with the col_index_num argument.
(6) col_index_num argument.
The:
 Third argument of the VLOOKUP function.
 Number of the column (in the table) containing the value to return.
 The first column in the table is column 1.
 The second column in the table is column 2.
 …
 The nth column in the table is column n.
(7) range_lookup argument.
The fourth argument of the VLOOKUP function. Optional argument.
Specifies whether you want an approximate or exact match.
If you omit the range_lookup argument, the VLOOKUP function:
 Returns an approximate match; but
 (Usually) Returns an exact match if:
 An exact match exists; and
 The table is sorted in ascending order.
Cues to Carry Out a VLOOKUP with Multiple Criteria (with the VLOOKUP Function and a Helper Column)
 The helper column must (as a general rule) be:
 The first/leftmost column in the table.
 To the left of the column containing the value to return.
 As a general rule, you can use either of the following constructs to concatenate the data in the applicable table columns (the columns containing the multiple criteria):
 The ampersand (&) calculation operator. This is the construct I use in the structure/template above.
 The CONCATENATE function.
 When concatenating criteria, consider using a separator between the individual criteria. A unique separator helps:
 Improve the likelihood that the concatenated criteria result in unique entries. As a general rule, if the helper column contains duplicate values (and you look up one of those duplicate values), the VLOOKUP function works with the first entry matching the lookup value (containing the concatenated criteria).
 Improve the readability of the concatenated criteria (in the helper column).
 The VLOOKUP function is case insensitive.
 Ensure the basic structure (including any separator) you use to specify the lookup_value argument matches the basic structure you use to concatenate the data in the helper column. In other words: Concatenate the multiple criteria in the same way (both) when:
 Setting up the helper column; and
 Specifying the lookup_value argument.
 If the VLOOKUP function fails to find an exact match for the lookup value (containing the concatenated criteria) in the first/leftmost column of the table (the helper column), it returns the #N/A error.
 To carry out a VLOOKUP with multiple criteria (with the VLOOKUP function and a helper column), set the range_lookup argument to FALSE.
Worksheet Formula Examples to Carry Out a VLOOKUP with Multiple Criteria (with the VLOOKUP Function and a Helper Column)
The worksheet formulas below carry out a VLOOKUP with multiple criteria (with the VLOOKUP function and a helper column) by specifying VLOOKUP's arguments as follows:
 lookup_value: A string formed by concatenating the following 3 items ($M$9&”  “&$M$10):
 The value stored in cell M9 ($M$9).
 A space, followed by a pipe (vertical bar; ), followed by a space (”  “).
 The value stored in cell M10 ($M$10).
 table_array: The table in cells B9 to I28 ($B$9:$I$28).
 col_index_num: The column number stored in column K (K11, K12, K13, K14, K15).
 range_lookup: FALSE.
Worksheet Formula  
1 

2 

3 

4 

5 

The first column in the table specified as table_array (column B) is a helper column. The helper column concatenates the multiple criteria in the same way as the lookup_value. In other words: Cells in the helper column contain a string formed by concatenating 3 items:
 The value stored in the column with the first VLOOKUP criterion.
 A space, followed by a pipe (vertical bar; ), followed by a space (”  “).
 The value stored in the column with the second VLOOKUP criterion.
Effects of Worksheet Formula Examples to Carry Out a VLOOKUP with Multiple Criteria (with the VLOOKUP Function and a Helper Column)
The image below illustrates the effects of working with the worksheet formula examples.
In this example:
 Cells A8 to I28 contain a table with the following characteristics:
 9 columns (Helper Formula, Helper Column (Product  City), Salesperson, Date, Customer, Product, City, Units, Unit Price).
 1 header row (row 8).
 20 entries (rows 9 to 28).
 The table stored in cells A8 to I28 contains 2 helper columns:
 Column B (Helper Column (Product  City)):
 Is the helper column used to carry out a VLOOKUP with multiple criteria.
 Cells in the helper column contain a string formed by concatenating 3 items:
 The value stored in column F (F9, F10, F11, …, F28).
 A space, followed by a pipe (vertical bar; ), followed by a space (”  “).
 The value stored in column G (G9, G10, G11, …, G28).
 Column A (Helper Formula):
 Is a helper column used for illustration purposes, in the context of this example.
 Displays the worksheet formulas stored in the helper column (column B).
 Column B (Helper Column (Product  City)):
 Cells K8 to N15 contain a table with the following characteristics:
 4 columns (Column, Header, Value, Formula).
 Cells M9 and M10 contain the 2 (multiple) VLOOKUP criteria.
 Cells K11 to K15 contain numbers. These numbers match the column numbers (see cells B6 to I6) of the first table in the worksheet (the table VLOOKUP works with).
 Cells M11 to M15 contain the example worksheet formulas (working with the VLOOKUP function).
 Cells N11 to N15 display the example worksheet formulas.
The example worksheet formulas (in cells M11 to M15) do the following:
 Search for the lookup value (containing the concatenated criteria; $M$9&”  “&$M$10) in the table's helper column (column B). The lookup value (containing the concatenated criteria) is found in cell B24.
 Return the values in the same row (row 24 of the worksheet) but in the columns specified in cells K11 to K15 as follows:
 Cell M11: Value in the second column of the table (Salesperson 32 in cell C24).
 Cell M12: Value in the third column of the table (16Jan14 in cell D24).
 Cell M13: Value in the fourth column of the table (Customer 22 in cell E24).
 Cell M14: Value in the seventh column of the table (41.00 in cell H24).
 Cell M15: Value in the eighth column of the table (899.99 in cell I24).
Excel VLOOKUP Multiple Criteria (with Array Formula, and INDEX and MATCH Functions)
Worksheet Formula to Carry Out a VLookup with Multiple Criteria (with an Array Formula, and the INDEX and MATCH Functions)
To carry out a VLookup with multiple criteria (with an array formula, and the INDEX and MATCH functions), use the following structure/template in the applicable worksheet formula.
=INDEX(ColumnWithValueToReturn,MATCH(1,(Criterion1=ColumnWithCriterion1)*(Criterion2=ColumnWithCriterion2)*(Criterion3=ColumnWithCriterion3)*…*(Criterion#=ColumnWithCriterion#),0))
This is an array formula. If you're not working with Excel 365, you may have to enter this formula by pressing Ctrl + Shift + Enter. In these cases, Excel wraps the worksheet formula in curly braces ({}).
{=INDEX(ColumnWithValueToReturn,MATCH(1,(Criterion1=ColumnWithCriterion1)*(Criterion2=ColumnWithCriterion2)*(Criterion3=ColumnWithCriterion3)*…*(Criterion#=ColumnWithCriterion#),0))}
Process to Carry Out a VLOOKUP with Multiple Criteria (with the VLOOKUP Function and a Helper Column)
 Build an array with 0s and 1s as follows:
 Carry out as many logical tests as required (1 logical test per criterion) to compare each of the multiple criteria against the cells in a column (containing the respective criterion). Each individual logical test returns TRUE or FALSE as follows:
 TRUE if the value in the applicable cell/row in the column is equal to the applicable criterion.
 FALSE if the value in the applicable cell/row in the column isn't equal to the applicable criterion.
 Carry out a logical conjunction of the individual logical tests. To do this, create an expression that returns the following:
 1 (TRUE) if each individual cell/column in the applicable row is equal to the applicable (respective) criterion. In other words: Test whether the row meets (all) the multiple criteria.
 0 (FALSE) if any individual cell/column in the applicable row is not equal to the applicable (respective) criterion. In other words: Test whether any cell in the row doesn't meet the applicable criterion.
 Carry out as many logical tests as required (1 logical test per criterion) to compare each of the multiple criteria against the cells in a column (containing the respective criterion). Each individual logical test returns TRUE or FALSE as follows:
 Use the MATCH function to identify the row number (in the cell range the INDEX and MATCH work with) where all the criteria are met (the values in all applicable cells/columns of that row are equal to the applicable criteria).
 Use the INDEX function to return a value inside the column containing the values to return. The value returned by the INDEX function should be that in the row where:
 Each individual cell/column is equal to the applicable (respective) criterion; and
 (All) The multiple criteria are met.
Main Constructs to Carry Out a VLookup with Multiple Criteria (with an Array Formula, and the INDEX and MATCH Functions)
(1) Equal to (=) comparison operator.
Compares 2 expressions and returns a logical value (TRUE or FALSE) as follows:
 TRUE if the 2 expressions are equal (Expression1=Expression2).
 FALSE if the 2 expressions aren't equal (Expression 1 <> Expression2).
(2) Multiplication (*) arithmetic operator.
As a general rule, multiplies 2 numbers.
When multiplying Boolean values (TRUE and FALSE), the multiplication operator converts the Boolean values into 1s and 0s as follows:
 TRUE is converted to 1.
 FALSE is converted to 0.
When carrying out a VLookup with multiple criteria (with an array formula, and the INDEX and MATCH functions), the asterisk (*) acts as the And logical operator. In other words, the expression working with the asterisk (*) returns TRUE or FALSE as follows:
 TRUE if all evaluated conditions return TRUE.
 FALSE if any (or all) evaluated condition(s) returns FALSE.
(3) MATCH function.
When carrying out a VLookup with multiple criteria (with an array formula, and the INDEX and MATCH functions), the MATCH function does the following:
 Search for an item in an array; and
 Return the relative position of the item in the array.
(4) lookup_value argument.
The:
 First argument of the MATCH function.
 Value you look up or search for (the lookup value) in the array (lookup_array).
(5) lookup_array argument.
The:
 Second argument of the MATCH function.
 Array where you search for the lookup value.
(6) match_type argument.
The third argument of the MATCH function. Optional argument.
Specifies how Excel matches the lookup value (lookup_value) with the values in the array (lookup_array).
(7) INDEX function.
When carrying out a VLookup with multiple criteria (with an array formula, and the INDEX and MATCH functions), the INDEX function returns the value in a cell at the intersection of a particular row and column in a cell range.
The worksheet function structure/template I describe assumes the cell range the INDEX function works with is a singlecolumn cell range (1 column, several rows). In such cases, the INDEX function returns the value in a specific rows of the applicable column.
(8) array argument.
The:
 First argument of the INDEX function.
 Cell range containing the value to return (the column with the applicable data).
(9) row_num argument.
The:
 Second argument of the INDEX function.
 Row (in the cell range specified as array) from where the value is returned.
Cues to Carry Out a VLookup with Multiple Criteria (with an Array Formula, and the INDEX and MATCH Functions)
 This worksheet formula structure/template is an array formula. If you're not working with Excel 365, you may have to enter this formula by pressing Ctrl + Shift + Enter. In these cases, Excel wraps the worksheet formula in curly braces ({}).
 You rely on Boolean logic to specify the lookup_array argument of the MATCH function ((Criterion1=ColumnWithCriterion1)*(Criterion2=ColumnWithCriterion2)*(Criterion3=ColumnWithCriterion3)*…*(Criterion#=ColumnWithCriterion#)). This is an array with 0s and 1s.
 Rows where any of the criteria are not met return a 0.
 Rows where all criteria are met return a 1.
 As a general rule, specify the following as single column cell ranges (1 column, several rows):
 The cell ranges used to carry out the equal to comparisons when building the lookup_array argument of the MATCH function.
 The array argument of the INDEX function.
 As a general rule, if the criteria columns you work with (to specify the lookup_array argument of the MATCH function) contain duplicate values (several rows match the multiple criteria, resulting in several rows matching the lookup value of 1), the MATCH function works with the first entry matching the lookup value (1).
 The MATCH function is case insensitive (when matching text values).
 If the MATCH function fails to find an exact match for the lookup value (1), it returns the #N/A error.
 To carry out a VLookup with multiple criteria (with an array formula, and the INDEX and MATCH functions), set the match_type argument of the MATCH function to 0.
Worksheet Formula Examples to Carry Out a VLookup with Multiple Criteria (with an Array Formula, and the INDEX and MATCH Functions)
The worksheet formulas below carry out a VLookup with multiple criteria (with an array formula, and the INDEX and MATCH functions) by specifying INDEX's and MATCH's arguments as follows:
(1) MATCH function.
 lookup_value: 1.
 lookup_array: The product of multiplying the Boolean values (TRUE or FALSE) returned by 2 equal to comparisons. In this multiplication, TRUE is converted to 1 and FALSE is converted to 0.
 Factor 1: An array of Boolean values (TRUE or FALSE) comparing the value in cell K8 ($K$8) with each value in cells D8 to D27 ($D$8:$D$27), and returning TRUE or FALSE as follows:
 TRUE if the value in cell K8 is equal to the value in the applicable cell/row in column D (K8 = D#).
 FALSE if the value in cell K8 isn't equal to the value in the applicable cell/row in column D (K8 <> D#).
 Factor 2: An array of Boolean values (TRUE or FALSE) comparing the value in cell K9 ($K$9) with each value in cells E8 to E27 ($E$8:$E$27), and returning TRUE or FALSE as follows:
 TRUE if the value in cell K9 is equal to the value in the applicable cell/row in column E (K9 = E#).
 FALSE if the value in cell K9 isn't equal to the value in the applicable cell/row in column E (K9 <> E#).
 Factor 1: An array of Boolean values (TRUE or FALSE) comparing the value in cell K8 ($K$8) with each value in cells D8 to D27 ($D$8:$D$27), and returning TRUE or FALSE as follows:
 match_type: 1.
(2) INDEX function.
 array: A singlecolumn cell range (1 column, 20 rows), as follows:
 Cells A8 to A27 (A$8:A$27).
 Cells B8 to B27 (B$8:B$27).
 Cells C8 to C27 (C$8:C$27).
 Cells F8 to F27 (F$8:F$27).
 Cells G8 to G27 (G$8:G$27).
 row_num: The value (number) returned by the MATCH function.
These worksheet formulas are entered in Excel 365. If you're not working with Excel 365, you may have to enter this formula by pressing Ctrl + Shift + Enter. In these cases, Excel wraps the worksheet formula in curly braces ({}).
Worksheet Formula  
1 

2 

3 

4 

5 

Effects of Worksheet Formula Examples to Carry Out a VLookup with Multiple Criteria (with an Array Formula, and the INDEX and MATCH Functions)
The image below illustrates the effects of working with the worksheet formula examples.
In this example:
 Cells A7 to G27 contain a table with the following characteristics:
 7 columns (Salesperson, Date, Customer, Product, City, Units, Unit Price).
 1 header row (row 7).
 20 entries (rows 8 to 27).
 Cells I7 to L14 contain a table with the following characteristics:
 4 columns (Column, Header, Value, Formula).
 Cells K8 and K9 contain the 2 (multiple) VLookup criteria.
 Cells I8 to I14 contain letters. These letters match the column headers of the first table in the worksheet (the table INDEX and MATCH work with).
 Cells K10 to K14 contain the example worksheet formulas (working with the INDEX and MATCH functions).
 Cells L10 to L14 display the example worksheet formulas. These worksheet formulas are entered in Excel 365. If you're not working with Excel 365, you may have to enter this formula by pressing Ctrl + Shift + Enter. In these cases, Excel wraps the worksheet formula in curly braces ({}).
The example worksheet formulas (in cells L10 to L14) do the following:
 Find the row where the following 2 (multiple) conditions are met:
 The value in that row of column D (cells D8 to D27) is equal to the value in cell K8.
 The value in that row of column E (cells E8 to E27) is equal to the value in cell K9.
 Return the values in row 23 of the worksheet (row 16 of the applicable singlecolumn cell range) as follows:
 Cell K10: Value in column A (Salesperson 32 in cell A23).
 Cell K11: Value in column B (16Jan14 in cell B23).
 Cell K12: Value in column C (Customer 22 in cell C23).
 Cell K13: Value in column F (41.00 in cell F23).
 Cell K14: Value in column G (899.99 in cell G23).