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.
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).