In this Excel VLOOKUP Tutorial, you **learn how to use the VLOOKUP function in your worksheet formulas** to:

- Look for a value in the first column of a table; and
- Return a value in the same row but from another column you specify.

This Excel VLOOKUP Tutorial is accompanied by Excel workbooks containing the data and formulas I use in the examples below. You can **get immediate free access to these example workbooks** by subscribing to the Power Spreadsheets Newsletter.

Use the following Table of Contents to navigate to the section you're interested in.

Table of Contents

- Related Excel Tutorials
- #1: VLOOKUP exact match
- #2: VLOOKUP approximate match
- #3: VLOOKUP with multiple criteria
- #4: VLOOKUP for second, third, or nth match
- #5: VLOOKUP replacing #N/A error
- #6: VLOOKUP partial match (with wildcards)
- #7: VLOOKUP with named range
- #8: VLOOKUP with column header (vs. column number)
- VLOOKUP with column header (vs. column number) formula
- VLOOKUP with column header (vs. column number) process
- VLOOKUP with column header (vs. column number) formula explanation
- VLOOKUP with column header (vs. column number) formula example
- Effects of using VLOOKUP with column header (vs. column number) formula example

- #9: VLOOKUP from another sheet
- #10: VLOOKUP from another sheet with variable sheet name
- VLOOKUP from another sheet with variable sheet name formula
- VLOOKUP from another sheet with variable sheet name process
- VLOOKUP from another sheet with variable sheet name formula explanation
- VLOOKUP from another sheet with variable sheet name formula example
- Effects of using VLOOKUP from another sheet with variable sheet name formula example

- #11: VLOOKUP from another workbook
- Learn more about working with the VLOOKUP function in Excel

## Related Excel Tutorials

The following Tutorials may help you better understand and implement the contents below:

- Formulas and functions:
- Macros and VBA:

You can find additional Tutorials in the Archives.

## #1: VLOOKUP exact match

### VLOOKUP exact match formula

To **carry out a VLookup that looks up for an exact match**, use a formula with the following structure:

`=VLOOKUP(LookupValue,LookupTable,ColumnIndexNumber,FALSE)` |

### VLOOKUP exact match process

To **carry out a VLookup that looks up for an exact match**, follow these steps:

- Specify the value you want to look up (LookupValue) in the first (leftmost) column of a table (LookupTable).
- Identify the cell range (a table array) containing the lookup table (LookupTable).
- Specify the index number of the column (within LookupTable) from which you want to obtain a value (ColumnIndexNumber).
- Specify that VLOOKUP searches for an exact match of LookupValue (FALSE).

### VLOOKUP exact match formula explanation

##### Item: VLOOKUP

The VLOOKUP function does the following:

- Looks for a value (LookupValue) in the first (leftmost) column of a table (LookupTable); and
- Returns a value in the same row but from another column you specify (ColumnIndexNumber).

##### Item: LookupValue

The lookup_value argument of the VLOOKUP function (LookupValue) is the value you look up in the first (leftmost) column of LookupTable. In other words, LookupValue must be in the first column of the cell range you specify as LookupTable.

You can specify LookupValue as either:

- A value;
- A text string; or
- A cell reference.

##### Item: LookupTable

The table_array argument of the VLOOKUP function (LookupTable) is the cell range in which VLOOKUP searches for the following:

- The LookupValue in the first column of LookupTable; and
- The value to return in the column you specify (ColumnIndexNumber).

Therefore, the cell range you specify as LookupTable must include both of the following columns:

- The first column, which must contain the LookupValue; and
- The column from which VLOOKUP should return a value.

##### Item: ColumnIndexNumber

The col_index_num argument of the VLOOKUP function (ColumnIndexNumber) is the column number within the LookupTable from which VLOOKUP returns a value, as follows:

Column | ColumnIndexNumber | Comments |

First | 1 | Must contain the LookupValue. |

Second | 2 | |

Third | 3 | |

… | … | |

#th | # |

##### Item: FALSE

The range_lookup argument of the VLOOKUP function specifies whether VLOOKUP searches for an approximate or an exact match for LookupValue in the first column of LookupTable.

Set the range_lookup argument to FALSE when searching for an exact match.

### VLOOKUP exact match formula example

The worksheet formulas below carry out a series of exact match VLookups as follows:

- LookupValue: The value specified in cell I8 ($I$8).
- LookupTable: The lookup table in cells A8 to E57 ($A$8:$E$57).
- ColumnIndexNumber: The column number specified in column G (G9, G10, G11 and G12).

No. | VLookup formula |

1 | `=VLOOKUP($I$8,$A$8:$E$57,G9,FALSE)` |

2 | `=VLOOKUP($I$8,$A$8:$E$57,G10,FALSE)` |

3 | `=VLOOKUP($I$8,$A$8:$E$57,G11,FALSE)` |

4 | `=VLOOKUP($I$8,$A$8:$E$57,G12,FALSE)` |

### Effects of using VLOOKUP exact match formula example

The following image illustrates the results returned by the exact match VLookup formulas. As expected, the formulas (in cells I9 to I12) do the following:

- Look for the value (LookupValue) specified in cell I8 (Marie Kelly) in the first column (column A) of the lookup table (LookupTable).
- Return the values in the same row but from the columns specified in column G (ColumnIndexNumber), as follows:
- Cell I9: The value in the second (G9) column of the lookup table (Surface Laptop).
- Cell I10: The value in the third (G10) column of the lookup table (Chicago).
- Cell I11: The value in the fourth (G11) column of the lookup table (300).
- Cell I12: The value in the fifth (G12) column of the lookup table ($1,499).

## #2: VLOOKUP approximate match

### VLOOKUP approximate match formula

To **carry out a VLookup that looks up for an approximate match**, use a formula with the following structure:

`=VLOOKUP(LookupValue,LookupTable,ColumnIndexNumber,TRUE)` |

### VLOOKUP approximate match process

To **carry out a VLookup that looks up for an approximate match**, follow these steps:

- Specify the value you want to look up (LookupValue) in the first (leftmost) column of a table (LookupTable).
- Identify the cell range (a table array) containing the lookup table (LookupTable).
- Specify the index number of the column (within Lookup Table) from which you want to obtain a value (ColumnIndexNumber).
- Specify that VLOOKUP searches for an approximate match of LookupValue (TRUE).

### VLOOKUP approximate match formula explanation

##### Item: VLOOKUP

The VLOOKUP function does the following:

- Looks for a value (LookupValue) in the first (leftmost) column of a table (LookupTable); and
- Returns a value in the same row but from another column you specify (ColumnIndexNumber).

##### Item: LookupValue

The lookup_value argument of the VLOOKUP function (LookupValue) is the value you look up in the first (leftmost) column of LookupTable. In other words, LookupValue must be in the first column of the cell range you specify as LookupTable.

You can specify LookupValue as either:

- A value;
- A text string; or
- A cell reference.

When looking for an approximate match with VLOOKUP:

- VLOOKUP searches for the closest match that is less than or equal to LookupValue in the first column of LookupTable; and
- Therefore, the data in the first column of LookupTable must be sorted in ascending order

##### Item: LookupTable

The table_array argument of the VLOOKUP function (LookupTable) is the cell range in which VLOOKUP searches for the following:

- The closest match that is less than or equal to LookupValue in the first column of LookupTable; and
- The value to return in the column you specify (ColumnIndexNumber).

Therefore, the cell range you specify as LookupTable must include both of the following columns:

- The first column, which must be sorted in ascending order; and
- The column from which VLOOKUP should return a value.

##### Item: ColumnIndexNumber

The col_index_num argument of the VLOOKUP function (ColumnIndexNumber) is the column number within the LookupTable from which VLOOKUP returns a value, as follows:

Column | ColumnIndexNumber | Comments |

First | 1 | Must be sorted in ascending order. |

Second | 2 | |

Third | 3 | |

… | … | |

#th | # |

##### Item: TRUE

The range_lookup argument of the VLOOKUP function specifies whether VLOOKUP searches for an approximate or an exact match for LookupValue in the first column of LookupTable.

Set the range_lookup argument to TRUE when searching for an approximate match.

### VLOOKUP approximate match formula example

The worksheet formulas below carry out a series of approximate match VLookups as follows:

- LookupValue: The value specified in column F (F8, F9, F10, F11, F12, …, F57).
- LookupTable: The lookup table in cells K8 to L14 ($K$8:$L$14).
- ColumnIndexNumber: 2.

No. | VLookup formula |

1 | `=VLOOKUP(F8,$K$8:$L$14,2,TRUE)` |

2 | `=VLOOKUP(F9,$K$8:$L$14,2,TRUE)` |

3 | `=VLOOKUP(F10,$K$8:$L$14,2,TRUE)` |

4 | `=VLOOKUP(F11,$K$8:$L$14,2,TRUE)` |

5 | `=VLOOKUP(F12,$K$8:$L$14,2,TRUE)` |

… | … |

50 | `=VLOOKUP(F57,$K$8:$L$14,2,TRUE)` |

### Effects of using VLOOKUP approximate match formula example

The following image illustrates the results returned by the approximate match VLookup formulas. As expected, the formulas (in cells G8 to G57) do the following:

- Look for an approximate match (the closest match that is less than or equal) to the value (LookupValue) specified in column F (Total Sales) in the first column (column K) of the lookup table (LookupTable).
- Return the value in the same row as the approximate match but from the second column (2) of the lookup table (Commission).

## #3: VLOOKUP with multiple criteria

### VLOOKUP with multiple criteria formula

To **carry out a VLookup that considers multiple criteria**, use a formula with the following structure:

`=VLOOKUP(ConcatenatedLookupValue,LookupTableWithHelperColumn,ColumnIndexNumber,FALSE)` |

When carrying out a VLookup with multiple criteria, do the following:

- Add a helper column as the first (leftmost) column of the lookup table.
- In this helper column, concatenate the contents of the cells containing the multiple criteria your VLookup considers.

To (i) concatenate the contents of the cells containing the multiple criteria and (ii) build the concatenated value you look up for, use a formula with the following structure:

`=Criteria1&Criteria2&...&Criteria#` |

### VLOOKUP with multiple criteria process

To **carry out a VLookup that considers multiple criteria**, follow these steps:

- Identify the cell range (a table array) containing the lookup table.
- Add a helper column as the first (leftmost) column of the lookup table.
- Use the helper column to concatenate the contents of the cells containing the multiple criteria your VLookup considers (Criteria1&Criteria2&…&Criteria#).
- Concatenate the multiple criteria you want to look up (Criteria1&Criteria2&…&Criteria#) and use this single concatenated string to specify the value you want to look up (ConcatenatedLookupValue) in the helper column of the lookup table.
- Identify the lookup table (LookupTableWithHelperColumn) including the helper column.
- Specify the index number of the column (within LookupTableWithHelperColumn) from which you want to obtain a value (ColumnIndexNumber).
- Specify that VLOOKUP searches for an exact match of ConcatenatedLookupValue (FALSE).

### VLOOKUP with multiple criteria formula explanation

#### Formula #1: VLOOKUP(ConcatenatedLookupValue,LookupTableWithHelperColumn,ColumnIndexNumber,FALSE)

##### Item: VLOOKUP

The VLOOKUP function does the following:

- Looks for a value built by concatenating multiple criteria (ConcatenatedLookupValue) in a helper (the first or leftmost) column of a table (LookupTableWithHelperColumn); and
- Returns a value in the same row but from another column you specify (ColumnIndexNumber).

##### Item: ConcatenatedLookupValue

The lookup_value argument of the VLOOKUP function (ConcatenatedLookupValue) is the value you look up in the helper column of LookupTableWithHelperColumn. Therefore:

- Build ConcatenatedLookupValue by concatenating the multiple criteria.
- ConcatenatedLookupValue must be in the first column of the cell range you specify as LookupTableWithHelperColumn. For these purposes, do the following:
- Add a helper column as the first (leftmost) column of LookupTableWithHelperColumn.
- In this helper column, concatenate the contents of the cells containing the multiple criteria.

You can use the text concatenation operator (&) (as explained below) to both:

- Specify ConcatenatedLookupValue; and
- Build the helper column in LookupTableWithHelperColumn.

##### Item: LookupTableWithHelperColumn

The table_array argument of the VLOOKUP function (LookupTableWithHelperColumn) is the cell range in which VLOOKUP searches for the following:

- The ConcatenatedLookupValue in the helper (first) column of LookupTableWithHelperColumn; and
- The value to return in the column you specify (ColumnIndexNumber).

Therefore, the cell range you specify as LookupTableWithHelperColumn must include both of the following columns:

- The helper (first) column, which must contain the ConcatenatedLookupValue; and
- The column from which VLOOKUP should return a value.

##### Item: ColumnIndexNumber

The col_index_num argument of the VLOOKUP function (ColumnIndexNumber) is the column number within the LookupTableWithHelperColumn from which VLOOKUP returns a value, as follows:

Column | ColumnIndexNumber | Comments |

First | 1 | Helper column built by concatenating the applicable multiple criteria. |

Second | 2 | |

Third | 3 | |

… | … | |

#th | # |

##### Item: FALSE

The range_lookup argument of the VLOOKUP function specifies whether VLOOKUP searches for an approximate or an exact match for ConcatenatedLookupValue in the first column of LookupTableWithHelperColumn.

Set the range_lookup argument to FALSE when searching for an exact match for multiple criteria.

#### Formula #2: Criteria1&Criteria2&…&Criteria#

The text concatenation operator (&) joins or concatenates several strings to produce a single continuous string.

When carrying out a VLookup with multiple criteria, use the & operator to:

- Concatenate the applicable multiple criteria (Criteria1, Criteria2, …, Criteria#); and
- Produce a single continuous string containing all the applicable criteria (Criteria1&Criteria2&…&Criteria#).

Follow this formula structure for both:

- Specifying ConcatenatedLookupValue; and
- Building the helper column in LookupTableWithHelperColumn.

### VLOOKUP with multiple criteria formula example

The worksheet formulas below carry out a series of VLookups that consider multiple criteria as follows:

- ConcatenatedLookupValue: The value specified in cell L8 ($L$8). This value is built by concatenating 2 criteria, following the same logic as the helper formulas I explain below.
- LookupTableWithHelperColumn: The lookup table in cells B8 to H57 ($B$8:$H$57), where column B is a helper column.
- ColumnIndexNumber: The column number specified in column J (J9, J10, J11, J12, J13 and J14).

No. | VLookup formula |

1 | `=VLOOKUP($L$8,$B$8:$H$57,J9,FALSE)` |

2 | `=VLOOKUP($L$8,$B$8:$H$57,J10,FALSE)` |

3 | `=VLOOKUP($L$8,$B$8:$H$57,J11,FALSE)` |

4 | `=VLOOKUP($L$8,$B$8:$H$57,J12,FALSE)` |

5 | `=VLOOKUP($L$8,$B$8:$H$57,J13,FALSE)` |

6 | `=VLOOKUP($L$8,$B$8:$H$57,J14,FALSE)` |

The helper column in the lookup table (column B) contains a worksheet formula that concatenates 2 criteria, which are specified in columns C and D. Therefore, the worksheet formulas in the helper column are as follows:

No. | Helper formula |

1 | `=C8&D8` |

2 | `=C9&D9` |

3 | `=C10&D10` |

4 | `=C11&D11` |

5 | `=C12&D12` |

… | … |

50 | `=C57&D57` |

### Effects of using VLOOKUP with multiple criteria formula example

The following image illustrates the results returned by the VLookup formulas with multiple criteria. As expected, the formulas (in cells L9 to L14) do the following:

- Look for the value (ConcatenatedLookupValue) specified in cell L8 by concatenating 2 criteria (MarieKelly) in the helper column (column B) of the lookup table (LookupTableWithHelperColumn).
- Return the values in the same row but from the columns specified in column J (ColumnIndexNumber), as follows:
- Cell L9: The value in the second (J9) column of the lookup table with helper column (Marie).
- Cell L10: The value in the third (J10) column of the lookup table with helper column (Kelly).
- Cell L11: The value in the fourth (J11) column of the lookup table with helper column (Surface Laptop).
- Cell L12: The value in the fifth (J12) column of the lookup table with helper column (Chicago).
- Cell L13: The value in the sixth (J13) column of the lookup table with helper column (300).
- Cell L14: The value in the seventh (J14) column of the lookup table with helper column ($1,499).

## #4: VLOOKUP for second, third, or nth match

### VLOOKUP for second, third, or nth match formula

To **carry out a VLookup that looks up the second, third, or nth match**, use a formula with the following structure:

`=VLOOKUP(LookupValue&Match#,LookupTableWithHelperColumn,ColumnIndexNumber,FALSE)` |

When carrying out a VLookup for the second, third, or nth match, do the following:

- Add a helper column as the first (leftmost) column of the lookup table.
- In this helper column, build a unique identifier by concatenating:
- The contents in the column containing the criterion your VLookup considers; and
- A counter that counts the number of times the contents in the current row have appeared in the column containing the criterion your VLookup considers.

To **build this unique identifier**, use a formula with the following structure:

`=CellInCriteriaColumnRelativeRef&COUNTIF(FirstCellInCriteriaColumnAbsoluteRef:CellInCriteriaColumnRelativeRef,CellInCriteriaColumnRelativeRef)` |

### VLOOKUP for second, third, or nth match process

To **carry out a VLookup that looks up the second, third, or nth match**, follow these steps:

- Identify the cell range (a table array) containing the lookup table.
- Add a helper column as the first (leftmost) column of the lookup table.
- Use the helper column to build a unique identifier by concatenating (i) the contents in the column containing the criterion your VLookup considers, and (ii) a counter that counts the number of times the contents in the current row have appeared in the column containing the criterion your VLookup considers.
- Concatenate the value and match number you want to look up and use this single concatenated string to specify the value you want to look up (LookupValue&Match#) in the helper column of the lookup table.
- Identify the lookup table (LookupTableWithHelperColumn) including the helper column.
- Specify the index number of the column (within LookupTableWithHelperColumn) from which you want to obtain a value (ColumnIndexNumber).
- Specify that VLOOKUP searches for an exact match of LookupValue&Match# (FALSE).

### VLOOKUP for second, third, or nth match formula explanation

#### Formula #1: VLOOKUP(LookupValue&Match#,LookupTableWithHelperColumn,ColumnIndexNumber,FALSE)

##### Item: VLOOKUP

The VLOOKUP function does the following:

- Looks for a value built by concatenating the criterion your VLookup considers and the match you look for (LookupValue&Match#) in a helper (the first or leftmost) column of a table (LookupTableWithHelperColumn); and
- Returns a value in the same row but from another column you specify (ColumnIndexNumber).

##### Item: LookupValue&Match#

The lookup_value argument of the VLOOKUP function (LookupValue&Match#) is the value you look up in the helper column of LookupTableWithHelperColumn. When looking for a specific match, build this argument by concatenating:

- The value you look for (LookupValue); and
- The match you look for (Match#).

If both LookupValue and Match# are stored in a cell, build this lookup_value argument with the following 3 items:

- LookupValue: The value you look for.
- &: The concatenation operator.
- Match#: The match you look for.

In other words, & concatenates LookupValue and Match# to produce a single continuous string containing both the value and match you look for.

LookupValue&Match# must be in the first column of LookupTableWithHelperColumn. For these purposes, do the following:

- Add a helper column as the first (leftmost) column of the lookup table.
- In this helper column, build a unique identifier by concatenating (i) the contents of the column containing the criterion your VLookup considers, and (ii) a counter of the number of (previous) matches for that criterion. Use formula #2 (as explained below) to build this unique identifier.

##### Item: LookupTableWithHelperColumn

The table_array argument of the VLOOKUP function (LookupTableWithHelperColumn) is the cell range in which VLOOKUP searches for the following:

- The unique identifier you build by concatenating the value you look for and the match you look for (LookupValue&Match#) in the helper (first) column of LookupTableWithHelperColumn; and
- The value to return in the column you specify (ColumnIndexNumber).

Therefore, the cell range you specify as LookupTableWithHelperColumn must include both of the following columns:

- The helper (first) column, which must contain the unique identifier you build by concatenating the value you look for and the match you look for (LookupValue&Match#); and
- The column from which VLOOKUP should return a value.

##### Item: ColumnIndexNumber

The col_index_num argument of the VLOOKUP function (ColumnIndexNumber) is the column number within the LookupTableWithHelperColumn from which VLOOKUP returns a value, as follows:

Column | ColumnIndexNumber | Comments |

First | 1 | Helper column with unique identifier built by concatenating (i) the contents of the column containing the criterion your VLookup considers, and (ii) a counter of the number of matches for that criterion. |

Second | 2 | |

Third | 3 | |

… | … | |

#th | # |

##### Item: FALSE

The range_lookup argument of the VLOOKUP function specifies whether VLOOKUP searches for an approximate or an exact match for the unique identifier you build by concatenating the value you look for and the match you look for (LookupValue&Match#) in the first column of LookupTableWithHelperColumn.

Set the range_lookup argument to FALSE when searching for the second, third, or nth match. This results in the VLOOKUP function searching for an exact match.

#### Formula #2: CellInCriteriaColumnRelativeRef&COUNTIF(FirstCellInCriteriaColumnAbsoluteRef:CellInCriteriaColumnRelativeRef,CellInCriteriaColumnRelativeRef)

##### Item: CellInCriteriaColumnRelativeRef

Relative reference to the cell at the intersection of:

- The row where you enter the formula; and
- The column containing the criterion your VLookup considers.

Within this formula structure, CellInCriteriaColumnRelativeRef plays 3 roles:

- The first part of the unique identifier you build in the helper column.
- The last cell in the cell range considered by the COUNTIF function when building the counter that counts the number of times the contents in the current row have appeared in the column containing the criterion your VLookup considers.
- The criteria argument of the COUNTIF function, which determines which cells are counted, when building the counter that counts the number of times the contents in the current row have appeared in the column containing the criterion your VLookup considers.

##### Item: &

The text concatenation operator (&) joins or concatenates several strings to produce a single continuous string.

When building the helper column in LookupTableWithHelperColumn, use the & operator to concatenate:

- The contents in the column containing the criterion your VLookup considers (CellInCriteriaColumnRelativeRef); and
- A counter of the number of matches for that criterion (COUNTIF(FirstCellInCriteriaColumnAbsoluteRef:CellInCriteriaColumnRelativeRef,CellInCriteriaColumnRelativeRef)).

##### Item: COUNTIF

The COUNTIF function counts the number of cells within a range (FirstCellInCriteriaColumnAbsoluteRef:CellInCriteriaColumnRelativeRef) that meet a criterion (CellInCriteriaColumnRelativeRef).

Therefore, COUNTIF returns the number of times the contents of CellInCriteriaColumnRelativeRef have appeared in the column containing the criterion your VLookup considers.

##### Item: FirstCellInCriteriaColumnAbsoluteRef:CellInCriteriaColumnRelativeRef

The range argument of the COUNTIF function is the cell range from which you want to count the cells that meet the applicable condition (CellInCriteriaColumnRelativeRef). When carrying out a VLookup that looks up the second, third, or nth match, build this cell range reference as follows:

- The first cell (FirstCellInCriteriaColumnAbsoluteRef) is the first cell in the column containing the criterion your VLookup considers. All the formulas in the helper column must refer to this same cell. Therefore, make the reference absolute ($ColumnLetter$RowNumber).
- The last cell (CellInCriteriaColumnRelativeRef, as explained above) is the cell at the intersection of:
- The row where you enter the formula; and
- The column containing the criterion your VLookup considers.

### VLOOKUP for second, third, or nth match formula example

The worksheet formulas below carry out a series of VLookups that look for the second match as follows:

- LookupValue: The value specified in cell K10 ($K$10).
- Match#: The value specified in cell K11 ($K$11).
- LookupTableWithHelperColumn: The lookup table in cells B8 to G57 ($B$8:$G$57), where column B is a helper column.
- ColumnIndexNumber: The column number specified in column I (I8, I9, I12 and I13).

No. | VLookup formula |

1 | `=VLOOKUP($K$10&$K$11,$B$8:$G$57,I8,FALSE)` |

2 | `=VLOOKUP($K$10&$K$11,$B$8:$G$57,I9,FALSE)` |

3 | `=VLOOKUP($K$10&$K$11,$B$8:$G$57,I12,FALSE)` |

4 | `=VLOOKUP($K$10&$K$11,$B$8:$G$57,I13,FALSE)` |

The helper column in the lookup table (column B) contains a worksheet formula that builds a unique identifier by concatenating the following:

- The contents in the column containing the criterion your VLookup considers (City, in column E); and
- A counter that counts (COUNTIF) the number of times the contents in the current row have appeared in the column (E) containing the criterion your VLookup considers.

Therefore, the worksheet formulas in the helper column are as follows:

No. | Helper formula |

1 | `=E8&COUNTIF($E$8:E8,E8)` |

2 | `=E9&COUNTIF($E$8:E9,E9)` |

3 | `=E10&COUNTIF($E$8:E10,E10)` |

4 | `=E11&COUNTIF($E$8:E11,E11)` |

5 | `=E12&COUNTIF($E$8:E12,E12)` |

… | … |

50 | `=E57&COUNTIF($E$8:E57,E57)` |

### Effects of using VLOOKUP for second, third, or nth match formula example

The following image illustrates the results returned by the VLookup formulas that look for the second match. As expected, the formulas (in cells K8, K9, K12 and K13) do the following:

- Look for the unique identifier specified by concatenating the contents in cells K10 (City) and K11 (Match No.) in the helper column (column B) of the lookup table (LookupTableWithHelperColumn).
- Return the values in the same row but from the columns specified in column I (ColumnIndexNumber), as follows:
- Cell K8: The value in the second (I8) column of the lookup table with helper column (Dorothy Carter).
- Cell K9: The value in the third (I9) column of the lookup table with helper column (Surface Book 2).
- Cell K12: The value in the fifth (I12) column of the lookup table with helper column (500).
- Cell K13: The value in the sixth (I13) column of the lookup table with helper column ($899).

## #5: VLOOKUP replacing #N/A error

### VLOOKUP replacing #N/A error formula

To **carry out a VLookup that handles possible #N/A errors**, use a formula with the following structure:

=IFNA(VLOOKUP(LookupValue,LookupTable,ColumnIndexNumber,RangeLookup),ValueIfNa) |

### VLOOKUP replacing #N/A error process

To **carry out a VLookup that handles possible #N/A errors**, follow these steps:

- Specify the value you want to look up (LookupValue) in the first (leftmost) column of a table (LookupTable).
- Identify the cell range (a table array) containing the lookup table (LookupTable).
- Specify the index number of the column (within LookupTable) from which you want to obtain a value (ColumnIndexNumber).
- Specify whether VLOOKUP searches for an exact or approximate match (RangeLookup).
- Specify that, if VLOOKUP returns the #N/A error (IFNA), another value (ValueIfNa) is returned.

### VLOOKUP replacing #N/A error formula explanation

#### Formula #1: VLOOKUP(LookupValue,LookupTable,ColumnIndexNumber,RangeLookup)

##### Item: VLOOKUP

The VLOOKUP function does the following:

- Looks for a value (LookupValue) in the first (leftmost) column of a table (LookupTable); and
- Returns a value in the same row but from another column you specify (ColumnIndexNumber).

##### Item: LookupValue

The lookup_value argument of the VLOOKUP function (LookupValue) is the value you look up in the first (leftmost) column of LookupTable. In other words, LookupValue must usually be in the first column of the cell range you specify as LookupTable.

If VLOOKUP doesn't find LookupValue in the first column of LookupTable, it usually returns the #N/A error.

You can specify LookupValue as either:

- A value;
- A text string; or
- A cell reference.

##### Item: LookupTable

The table_array argument of the VLOOKUP function (LookupTable) is the cell range in which VLOOKUP searches for the following:

- The LookupValue in the first column of LookupTable; and
- The value to return in the column you specify (ColumnIndexNumber).

Therefore, the cell range you specify as LookupTable must usually include both of the following columns:

- The first column, which must contain the LookupValue; and
- The column from which VLOOKUP should return a value.

If VLOOKUP doesn't find LookupValue in the first column of LookupTable, it usually returns the #N/A error.

##### Item: ColumnIndexNumber

The col_index_num argument of the VLOOKUP function (ColumnIndexNumber) is the column number within the LookupTable from which VLOOKUP returns a value, as follows:

Column | ColumnIndexNumber | Comments |

First | 1 | Must usually contain the LookupValue. Otherwise, VLOOKUP usually returns the #N/A error. |

Second | 2 | |

Third | 3 | |

… | … | |

#th | # |

##### Item: RangeLookup

The range_lookup argument of the VLOOKUP function (RangeLookup) specifies whether VLOOKUP searches for an approximate or an exact match for LookupValue in the first column of LookupTable.

- Set RangeLookup to TRUE when searching for an approximate match.
- Set RangeLookup to FALSE when searching for an exact match.

#### Formula #2: IFNA(VLOOKUP(…),ValueIfNa)

##### Item: IFNA

The IFNA function:

- Returns the value you specify if a formula or expression (VLOOKUP(…)) returns the #N/A error; and
- Returns the result of that formula (VLOOKUP(…)) otherwise.

In other words, IFNA does the following:

- Checks a formula (VLOOKUP(…)).
- If VLOOKUP(…) returns the #N/A error, IFNA returns a value you specify (ValueIfNa).
- If VLOOKUP(…) doesn't return the #N/A error, IFNA returns the result of that formula.

##### Item: VLOOKUP(…)

The value argument of the IFNA function (VLOOKUP(…)) is a value, formula, expression or reference that Excel checks for the #N/A error. For the explanation of this VLOOKUP function, please refer to the appropriate section in this Tutorial.

The VLOOKUP function may usually return an #N/A error when you either:

- Give an inappropriate value (including a value that isn't found in the first column of LookupTable) for the lookup_value argument (LookupValue); or
- Use the function to locate a value in a table that isn't properly sorted.

##### Item: ValueIfNa

The value_if_na argument of the IFNA function (ValueIfNa) is the value, formula, expression or reference that Excel returns if the value argument of the IFNA function (VLOOKUP(…)) evaluates to #N/A.

### VLOOKUP replacing #N/A error formula example

The worksheet formulas below carry out a series of exact match VLookups and handle possible #N/A errors as follows:

- LookupValue: The value specified in cell I8 ($I$8).
- LookupTable: The lookup table in cells A8 to E57 ($A$8:$E$57).
- ColumnIndexNumber: The column number specified in column G (G11 and G12).
- RangeLookup: FALSE.
- ValueIfNa: A zero-length string (“”).

No. | VLookup formula |

1 | `=IFNA(VLOOKUP($I$8,$A$8:$E$57,G11,FALSE),"")` |

2 | `=IFNA(VLOOKUP($I$8,$A$8:$E$57,G12,FALSE),"")` |

### Effects of using VLOOKUP replacing #N/A error formula example

The following image illustrates the results returned by the VLookup formulas that handle possible #N/A errors. As expected, the formulas (in cells I11 and I12) do the following:

- Look for the value (LookupValue) specified in cell I8 (Shawn Brooks) in the first column (column A) of the lookup table (LookupTable).
- The LookupValue (Shawn Brooks) isn't found in the first column of LookupTable. Therefore, the worksheet formulas return a zero-length string (“”).

Notice the difference between the results returned by the VLookup formulas that replace the #N/A error (cells I11 and I12) and the results returned by regular VLookup formulas in cells I9 and I10 (#N/A).

## #6: VLOOKUP partial match (with wildcards)

### VLOOKUP partial match (with wildcards) formula

To **carry out a VLookup that looks up a partial match and uses wildcards**, use a formula with the following structure:

`=VLOOKUP(LookupValueWithWildcards,LookupTable,ColumnIndexNumber,FALSE)` |

### VLOOKUP partial match (with wildcards) process

To **carry out a VLookup that looks up a partial match and uses wildcards**, follow these steps:

- Specify the value you want to look up (LookupValueWithWildcards) in the first (leftmost) column of a table (LookupTable) by working with wildcards (mainly * and ?).
- Identify the cell range (a table array) containing the lookup value (LookupTable).
- Specify the index number of the column (within LookupTable) from which you want to obtain a value (ColumnIndexNumber).
- Specify that VLOOKUP searches for an exact match of LookupValueWithWildcards (FALSE).

### VLOOKUP partial match (with wildcards) formula explanation

##### Item: VLOOKUP

The VLOOKUP function does the following:

- Looks for a value (LookupValueWithWildcards) in the first (leftmost) column of a table (LookupTable); and
- Returns a value in the same row but from another column you specify (ColumnIndexNumber).

##### Item: LookupValueWithWildcards

The lookup_value argument of the VLOOKUP function (LookupValueWithWildcards) is the value you look up in the first (leftmost) column of LookupTable. In other words, LookupValueWithWildcards must be in the first column of the cell range you specify as LookupTable.

When looking a partial match by using wildcards, you can use the following 2 wildcards when specifying LookupValueWithWildcards:

- ? (question mark): Represents any single character.
- * (asterisk): Represents any number of characters.

If, when specifying LookupValueWithWildcards, you must include a literal question mark, asterisk or tilde, precede the character with a tilde (~) as follows:

- ~?: Question mark (?).
- ~*: Asterisk (*).
- ~~: Tilde (~).

##### Item: LookupTable

- The LookupValueWithWilcards in the first column of LookupTable; and
- The value to return in the column you specify (ColumnIndexNumber).

Therefore, the cell range you specify as LookupTable must include both of the following columns:

- The first column, which must contain the LookupValueWithWildcards; and
- The column from which VLOOKUP should return a value.

##### Item: ColumnIndexNumber

Column | ColumnIndexNumber | Comments |

First | 1 | Must contain the LookupValueWithWildcards. |

Second | 2 | |

Third | 3 | |

… | … | |

#th | # |

##### Item: FALSE

The range_lookup argument of the VLOOKUP function specifies whether VLOOKUP searches for an approximate or an exact match for LookupValue in the first column of LookupTable.

Set the range_lookup argument to FALSE when searching searching for a partial match by working with wildcards.

### VLOOKUP partial match (with wildcards) formula example

The worksheet formulas below carry out a series of partial match VLookups with wildcards as follows:

- LookupValueWithWildcards: The value specified in cell I8 ($I$8) followed by any character sequence (&”*”).
- LookupTable: The lookup table in cells A8 to E57 ($A$8:$E$57).
- ColumnIndexNumber: The column number specified in column G (G9, G10, G11 and G12).

No. | VLookup formula |

1 | `=VLOOKUP($I$8&"*",$A$8:$E$57,G9,FALSE)` |

2 | `=VLOOKUP($I$8&"*",$A$8:$E$57,G10,FALSE)` |

3 | `=VLOOKUP($I$8&"*",$A$8:$E$57,G11,FALSE)` |

4 | `=VLOOKUP($I$8&"*",$A$8:$E$57,G12,FALSE)` |

### Effects of using VLOOKUP partial match (with wildcards) formula example

The following image illustrates the results returned by the partial match VLookup formulas with wildcards. As expected, the formulas (in cells I9 to I12) do the following:

- Look for the value specified in cell I8 (Mar) followed by any character sequences (&”*”) in the first column (column A) of the lookup table (LookupTable). In this example, the match found by VLOOKUP is Marie Kelly (cell A11).
- Return the values in the same row but from the columns specified in column G (ColumnIndexNumber), as follows:
- Cell I9: The value in the second (G9) column of the lookup table (Surface Laptop).
- Cell I10: The value in the third (G10) column of the lookup table (Chicago).
- Cell I11: The value in the fourth (G11) column of the lookup table (300).
- Cell I12: The value in the fifth (G12) column of the lookup table ($1,499).

## #7: VLOOKUP with named range

### VLOOKUP with named range formula

To **carry out a VLookup that works with a named range**, use a formula with the following structure:

`=VLOOKUP(LookupValue,RangeName,ColumnIndexNumber,RangeLookup)` |

### VLOOKUP with named range process

To **carry out a VLookup that works with a named range**, follow these steps:

- Identify the cell range (a table array) containing the lookup table.
- Define a name (RangeName) for the cell range containing the lookup table by, for example, (i) selecting the cell range and (ii) entering a name in the Name Box located at the left of Excel's formula bar. Use this RangeName when entering the VLOOKUP function.
- Specify the value you want to look up (LookupValue) in the first (leftmost) column of RangeName.
- Specify the index number of the column (within RangeName) from which you want to obtain a value (ColumnIndexNumber).
- Specify whether VLOOKUP searches for an exact or approximate match (RangeLookup).

### VLOOKUP with named range formula explanation

##### Item: VLOOKUP

The VLOOKUP function does the following:

- Looks for a value (LookupValue) in the first (leftmost) column of a named range (RangeName); and
- Returns a value in the same row but from another column you specify (ColumnIndexNumber).

##### Item: LookupValue

The lookup_value argument of the VLOOKUP function (LookupValue) is the value you look up in the first (leftmost) column of the lookup table. In other words, LookupValue must be in the first column of the named cell range representing the lookup table (RangeName).

You can specify LookupValue as either:

- A value;
- A text string; or
- A cell reference.

##### Item: RangeName

The table_array argument of the VLOOKUP function (RangeName) is the cell range in which VLOOKUP searches for the following:

- The LookupValue in the first column of RangeName; and
- The value to return in the column you specify (ColumnIndexNumber).

When working with a named range, use this named range as the table_array argument of the VLOOKUP function. This named cell range representing the lookup table (RangeName) must include both of the following columns:

- The first column, which must contain the LookupValue; and
- The column from which VLOOKUP should return a value.

##### Item: ColumnIndexNumber

The col_index_num argument of the VLOOKUP function (ColumnIndexNumber) is the column number within RangeName from which VLOOKUP returns a value, as follows:

Column | ColumnIndexNumber | Comments |

First | 1 | Must contain the LookupValue. |

Second | 2 | |

Third | 3 | |

… | … | |

#th | # |

##### Item: RangeLookup

The range_lookup argument of the VLOOKUP function (RangeLookup) specifies whether VLOOKUP searches for an approximate or an exact match for LookupValue in the first column of RangeName.

- Set RangeLookup to TRUE when searching for an approximate match.
- Set RangeLookup to FALSE when searching for an exact match.

### VLOOKUP with named range formula example

The worksheet formulas below carry out a series of VLookups working with a named range, as follows:

- LookupValue: The value specified in cell I8 ($I$8).
- RangeName: A named cell range (VLookupTable) representing cells A8 to E57.
- ColumnIndexNumber: The column number specified in column G (G9, G10, G11 and G12).
- RangeLookup: FALSE.

No. | VLookup formula |

1 | `=VLOOKUP($I$8,VLookupTable,G9,FALSE)` |

2 | `=VLOOKUP($I$8,VLookupTable,G10,FALSE)` |

3 | `=VLOOKUP($I$8,VLookupTable,G11,FALSE)` |

4 | `=VLOOKUP($I$8,VLookupTable,G12,FALSE)` |

### Effects of using VLOOKUP with named range formula example

The following image illustrates the results returned by the VLookup formulas that work with a named range. As expected, the formulas (in cells I9 to I12) do the following:

- Look for the value (LookupValue) specified in cell I8 (Marie Kelly) in the first column (column A) of the lookup table in the cell range named VLookupTable (RangeName).
- Return the values in the same row but from the columns specified in column G (ColumnIndexNumber), as follows:
- Cell I9: The value in the second (G9) column of the lookup table (Surface Laptop).
- Cell I10: The value in the third (G10) column of the lookup table (Chicago).
- Cell I11: The value in the fourth (G11) column of the lookup table (300).
- Cell I12: The value in the fifth (G12) column of the lookup table ($1,499).

## #8: VLOOKUP with column header (vs. column number)

### VLOOKUP with column header (vs. column number) formula

To **carry out a VLookup that works with a column's header (instead of its column number)**, use a formula with the following structure:

`=VLOOKUP(LookupValue,LookupTable,MATCH(ColumnHeader,HeaderRow,0),RangeLookup)` |

### VLOOKUP with column header (vs. column number) process

To **carry out a VLookup that works with a column's header (instead of its column number)**, follow these steps:

- Specify the header of the column from which you want to obtain a value (ColumnHeader).
- Identify the cell range containing the headers of the lookup table (HeaderRow).
- Search the header row of the lookup table (HeaderRow) for the first value that is exactly equal to ColumnHeader (0) and obtain the relative position (index number) of that column (MATCH). Use this value when entering the VLOOKUP function.
- Specify the value you want to look up (LookupValue) in the first (leftmost) column of the lookup table (LookupTable).
- Identify the cell range (a table array) containing the lookup table (LookupTable).
- Specify whether VLOOKUP searches for an exact or approximate match (RangeLookup).

### VLOOKUP with column header (vs. column number) formula explanation

#### Formula #1: MATCH(ColumnHeader,HeaderRow,0)

##### Item: MATCH

The MATCH function does the following:

- Searches for an item (ColumnHeader) in a cell range (HeaderRow); and
- Returns the relative position of that item (ColumnHeader) within the cell range (HeaderRow).

Therefore, MATCH returns the column number within LookupTable from which VLOOKUP returns a value.

##### Item: ColumnHeader

The lookup_value argument of the MATCH function (ColumnHeader) is the value you look for in the header row of the lookup table(HeaderRow). In other words, ColumnHeader is the column header you work with in your VLookup.

You can specify ColumnHeader as either:

- A value;
- A text string; or
- A cell reference.

##### Item: HeaderRow

The lookup_array argument of the MATCH function (HeaderRow) is the cell range in which MATCH searches for ColumnHeader. In other words, HeaderRow is the range of cells containing the headers of the lookup table.

##### Item: 0

The match_type argument of the MATCH function (0) specifies how the MATCH function matches ColumnHeader with the values in HeaderRow.

Set the match_type argument to 0 when carrying out a VLookup that works with column header (vs. column number). This results in the MATCH function finding the first value that is exactly equal to ColumnHeader.

##### Formula #2: VLOOKUP(LookupValue,LookupTable,MATCH(…),RangeLookup)

##### Item: VLOOKUP

The VLOOKUP function does the following:

- Looks for a value (LookupValue) in the first (leftmost) column of a table (LookupTable); and
- Returns a value in the same row but from another column whose header matches the one you specify (MATCH(…)).

##### Item: LookupValue

The lookup_value argument of the VLOOKUP function (LookupValue) is the value you look up in the first (leftmost) column of LookupTable. In other words, LookupValue must be in the first column of the cell range you specify as LookupTable.

You can specify LookupValue as either:

- A value;
- A text string; or
- A cell reference.

##### Item: LookupTable

- The LookupValue in the first column of LookupTable; and
- The value to return in the column whose header matches the one you specify (MATCH(…)).

Therefore, the cell range you specify as LookupTable must include both of the following columns:

- The first column, which must contain the LookupValue; and
- The column whose header matches the one you specify (MATCH()) and from which VLOOKUP should return a value.

##### Item: MATCH(…)

The col_index_num argument of the VLOOKUP function (MATCH(…)) is the column number within the LookupTable from which VLOOKUP returns a value. To work with a column's header (vs. a column number), use the MATCH function.

For the explanation of this MATCH formula, please refer to the appropriate section within this Tutorial.

##### Item: RangeLookup

The range_lookup argument of the VLOOKUP function (RangeLookup) specifies whether VLOOKUP searches for an approximate or an exact match for LookupValue in the first column of LookupTable.

- Set RangeLookup to TRUE when searching for an approximate match.
- Set RangeLookup to FALSE when searching for an exact match.

### VLOOKUP with column header (vs. column number) formula example

The worksheet formulas below carry out a series of VLookups that work with a column header (vs. a column number) as follows:

- LookupValue: The value specified in cell H7 ($H$7).
- LookupTable: The lookup table in cells A7 to E56 ($A$7:$E$56).
- ColumnHeader: The value specified in column G (G8, G9, G10 and G11).
- HeaderRow: The header row in cells A6 to E6 ($A$6:$E$6).
- RangeLookup: FALSE.

No. | VLookup formula |

1 | `=VLOOKUP($H$7,$A$7:$E$56,MATCH(G8,$A$6:$E$6,0),FALSE)` |

2 | `=VLOOKUP($H$7,$A$7:$E$56,MATCH(G9,$A$6:$E$6,0),FALSE)` |

3 | `=VLOOKUP($H$7,$A$7:$E$56,MATCH(G10,$A$6:$E$6,0),FALSE)` |

4 | `=VLOOKUP($H$7,$A$7:$E$56,MATCH(G11,$A$6:$E$6,0),FALSE)` |

### Effects of using VLOOKUP with column header (vs. column number) formula example

The following image illustrates the results returned by the VLookup formulas that work with column headers (vs. column numbers). As expected, the formulas (in cells H8 to H11) do the following:

- Look for the value (LookupValue) specified in cell H7 (Marie Kelly) in the first column (column A) of the lookup table (LookupTable).
- Return the values in the same row but from the columns whose headers (in HeaderRow) are specified in column G (ColumnHeader), as follows:
- Cell H8: The value in the column whose header is (G8) “Item” (Surface Laptop).
- Cell H9: The value in the column whose header is (G9) “City” (Chicago).
- Cell H10: The value in the column whose header is (G10) “No. Units Sold” (300).
- Cell H11: The value in the column whose header is (G11) “Average Sales Price per Unit” ($1,499).

## #9: VLOOKUP from another sheet

### VLOOKUP from another sheet formula

To **carry out a VLookup that works with data from another sheet**, use a formula with the following structure:

`=VLOOKUP(LookupValue,'WorksheetName'!LookupTableAddress,ColumnIndexNumber,RangeLookup)` |

### VLOOKUP from another sheet process

To **carry out a VLookup that works with data from another sheet**, follow these steps:

- Specify the value you want to look up (LookupValue) in the first (leftmost) column of the lookup table (‘WorksheetName'!LookupTableAddress).
- Identify the cell range (a table array) containing the lookup table using (i) the worksheet name wrapped within single quotes (‘) (‘WorksheetName'), (ii) an exclamation sign (!), and (iii) the address of the cell range (a table array) containing the lookup table (LookupTableAddress).
- Specify the index number of the column (within the lookup table) from which you want to obtain a value (ColumnIndexNumber).
- Specify whether VLOOKUP searches for an exact or approximate match (RangeLookup).

### VLOOKUP from another sheet formula explanation

##### Item: VLOOKUP

The VLOOKUP function does the following:

- Looks for a value (LookupValue) in the first (leftmost) column of a lookup table (‘WorksheetName'!LookupTableAddress); and
- Returns a value in the same row but from another column you specify (ColumnIndexNumber).

##### Item: LookupValue

The lookup_value argument of the VLOOKUP function (LookupValue) is the value you look up in the first (leftmost) column of the lookup table (‘WorksheetName'!LookupTableAddress). In other words, LookupValue must be in the first column of the lookup table you specify with ‘WorksheetName'!LookupTableAddress.

You can specify LookupValue as either:

- A value;
- A text string; or
- A cell reference.

##### Item: ‘WorksheetName'!LookupTableAddress

The table_array argument of the VLOOKUP function (‘WorksheetName'!LookupTableAddress) is the cell range in which VLOOKUP searches for the following:

- The LookupValue in the first column of the lookup table you specify with ‘WorksheetName'!LookupTableAddress; and
- The value to return in the column you specify (ColumnIndexNumber).

When carrying out a VLookup with data from another sheet, build this table_array argument with the following 3 items:

- ‘WorksheetName':
- WorksheetName is the name of the worksheet containing the lookup table.
- Single quotes (‘) aren't always required. They're, however, required when WorksheetName includes certain characters, such as spaces.

- !: When referring to a different sheet, separate the worksheet name (‘WorksheetName') and the actual cell address reference (LookupTableAddress) with an exclamation sign(!).
- LookupTableAddress: The address of the cell range containing the lookup table.

The lookup table you specify with ‘WorksheetName'!LookupTableAddress must include both of the following columns:

- The first column, which must contain the LookupValue; and
- The column from which VLOOKUP should return a value.

##### Item: ColumnIndexNumber

The col_index_num argument of the VLOOKUP function (ColumnIndexNumber) is the column number within the lookup table (‘WorksheetName'!LookupTableAddress) from which VLOOKUP returns a value, as follows:

Column | ColumnIndexNumber | Comments |

First | 1 | Must contain the LookupValue. |

Second | 2 | |

Third | 3 | |

… | … | |

#th | # |

##### Item: RangeLookup

The range_lookup argument of the VLOOKUP function (RangeLookup) specifies whether VLOOKUP searches for an approximate or an exact match for LookupValue in the first column of the lookup table (‘WorksheetName'!LookupTableAddress).

- Set RangeLookup to TRUE when searching for an approximate match.
- Set RangeLookup to FALSE when searching for an exact match.

### VLOOKUP from another sheet formula example

The worksheet formulas below carry out a series of VLookups with data from another sheet as follows:

- LookupValue: The value specified in cell C6 ($C$6).
- WorksheetName: VLOOKUP from another sheet data (‘VLOOKUP from another sheet data').
- LookupTableAddress: Cells A8 to E57 ($A$8:$E$57).
- ColumnIndexNumber: The column number specified in column A (A7, A8, A9 and A10).
- RangeLookup: FALSE.

No. | VLookup formula |

1 | `=VLOOKUP($C$6,'VLOOKUP from another sheet data'!$A$8:$E$57,A7,FALSE)` |

2 | `=VLOOKUP($C$6,'VLOOKUP from another sheet data'!$A$8:$E$57,A8,FALSE)` |

3 | `=VLOOKUP($C$6,'VLOOKUP from another sheet data'!$A$8:$E$57,A9,FALSE)` |

4 | `=VLOOKUP($C$6,'VLOOKUP from another sheet data'!$A$8:$E$57,A10,FALSE)` |

### Effects of using VLOOKUP from another sheet formula example

The following images illustrate the results returned by the VLookup formulas that work with data from another sheet. As expected, the formulas (in cells C7 to C10) do the following:

- Look for the value (LookupValue) specified in cell C6 (Marie Kelly) in the first column (column A) of the lookup table ($A$8:$E$57) in the “VLOOKUP from another sheet data” worksheet (‘VLOOKUP from another sheet data').
- Return the values in the same row but from the columns specified in column A (ColumnIndexNumber), as follows:
- Cell C7: The value in the second (A7) column of the lookup table (Surface Laptop).
- Cell C8: The value in the third (A8) column of the lookup table (Chicago).
- Cell C9: The value in the fourth (A9) column of the lookup table (300).
- Cell C10: The value in the fifth (A10) column of the lookup table ($1,499).

## #10: VLOOKUP from another sheet with variable sheet name

### VLOOKUP from another sheet with variable sheet name formula

To **carry out a VLookup that works with data from another sheet when the sheet name may vary and is specified in a worksheet cell**, use a formula with the following structure:

`=VLOOKUP(LookupValue,INDIRECT("'"&WorksheetNameCell&"'!"&"LookupTableAddress"),ColumnIndexNumber,RangeLookup)` |

### VLOOKUP from another sheet with variable sheet name process

To **carry out a VLookup that works with data from another sheet when the sheet name may vary and is specified in a worksheet cell**, follow these steps:

- Specify the value you want to look up (LookupValue) in the first (leftmost) column of the lookup table (INDIRECT(…)).
- Obtain a reference to the cell range (a table array) containing the lookup table by working with the INDIRECT function. Build the reference passed as argument of the INDIRECT function using:
- Single quotes (‘);
- Double quotes (“);
- The text concatenation operator (&);
- An exclamation sign (!);
- A reference to the cell containing the worksheet name (WorksheetNameCell); and
- The address of the cell range (a table array) containing the lookup table (LookupTableAddress).

- Specify the index number of the column (within the lookup table) from which you want to obtain a value (ColumnIndexNumber).
- Specify whether VLOOKUP searches for an exact or approximate match (RangeLookup).

### VLOOKUP from another sheet with variable sheet name formula explanation

#### Formula #1: VLOOKUP(LookupValue,INDIRECT(…),ColumnIndexNumber,RangeLookup)

##### Item: VLOOKUP

The VLOOKUP function does the following:

- Looks for a value (LookupValue) in the first (leftmost) column of a table (the lookup table returned by INDIRECT(…)); and
- Returns a value in the same row but from another column you specify (ColumnIndexNumber).

##### Item: LookupValue

The lookup_value argument of the VLOOKUP function (LookupValue) is the value you look up in the first (leftmost) column of the lookup table returned by INDIRECT(…). In other words, LookupValue must be in the first column of the lookup table you specify with INDIRECT(…).

You can specify LookupValue as either:

- A value;
- A text string; or
- A cell reference.

##### Item: INDIRECT(…)

The table_array argument of the VLOOKUP function (INDIRECT(…)) is the cell range in which VLOOKUP searches for the following:

- The LookupValue in the first column of the lookup table returned by INDIRECT(…); and
- The value to return in the column you specify (ColumnIndexNumber).

When carrying out a VLookup with data from another sheet when the sheet name may vary and is specified in a worksheet cell, build this table_array argument with the INDIRECT function.

For the explanation of this INDIRECT formula, please refer to the appropriate section within this Tutorial.

##### Item: ColumnIndexNumber

The col_index_num argument of the VLOOKUP function (ColumnIndexNumber) is the column number within the lookup table returned by INDIRECT(…) from which VLOOKUP returns a value, as follows:

Column | ColumnIndexNumber | Comments |

First | 1 | Must contain the LookupValue. |

Second | 2 | |

Third | 3 | |

… | … | |

#th | # |

##### Item: RangeLookup

The range_lookup argument of the VLOOKUP function (RangeLookup) specifies whether VLOOKUP searches for an approximate or an exact match for LookupValue in the first column of the lookup table returned by INDIRECT(…).

- Set RangeLookup to TRUE when searching for an approximate match.
- Set RangeLookup to FALSE when searching for an exact match.

#### Formula #2: INDIRECT(“‘”&WorksheetNameCell&”‘!”&”LookupTableAddress”)

##### Item: INDIRECT

The INDIRECT function returns a reference specified by a text string (“‘”&WorksheetNameCell&”‘!”&”LookupTableAddress”). Excel evaluates this reference to the contents of the referred cells.

You can generally use the INDIRECT function to dynamically change the reference to a cell or cell range within a formula without modifying the formula itself. Therefore, INDIRECT returns a dynamic reference to the lookup table.

##### Item: “‘”&WorksheetNameCell&”‘!”&”LookupTableAddress”

The ref_text argument of the INDIRECT function (“‘”&WorksheetNameCell&”‘!”&”LookupTableAddress”) is a reference to the cell range containing the lookup table.

You can specify ref_text as either:

- A reference to a cell containing a reference;
- A name defined as a reference; or
- A cell reference specified as a text string.

The formula structure in this VLOOKUP Tutorial works with both cell references (WorksheetNameCell) and text strings (‘, ‘! and LookupTableAddress). These text strings are wrapped within double quotes (“).

When carrying out a VLookup that works with another sheet and the sheet name is specified in a worksheet cell, build this ref_text argument by concatenating the 3 items listed below. For concatenation purposes, use the text concatenation operator (&), which joins or concatenates these several strings to produce a single continuous string.

- “‘” and “‘!”:
- When referring to a different sheet, separate the worksheet name (returned by WorksheetNameCell and wrapped within single quotes) and the actual cell address reference (LookupTableAddress) with an exclamation sign (!).
- Single quotes (‘) aren't always required. They're however, required when the worksheet name returned by WorksheetNameCell contains certain characters, such as spaces.

- WorksheetNameCell: Reference to the cell containing the name of the worksheet containing the lookup table.
- “LookupTableAddress”: The address of the cell range containing the lookup table.

The cell range returned by INDIRECT must include both of the following columns:

- The first column, which must contain the LookupValue; and
- The column from which VLOOKUP should return a value.

### VLOOKUP from another sheet with variable sheet name formula example

The worksheet formulas below carry out a series of VLookups with data from another sheet (whose name is specified in a cell) as follows:

- LookupValue: The value specified in cell C13 ($C$13).
- WorksheetNameCell: The name specified in cell C19 ($C$19).
- LookupTableAddress: Cells A8 to E57 ($A$8:$E$57).
- ColumnIndexNumber: The column number specified in column A (A14, A15, A16 and A17).
- RangeLookup: FALSE.

No. | VLookup formula |

1 | `=VLOOKUP($C$13,INDIRECT("'"&$C$19&"'!"&"$A$8:$E$57"),A14,FALSE)` |

2 | `=VLOOKUP($C$13,INDIRECT("'"&$C$19&"'!"&"$A$8:$E$57"),A15,FALSE)` |

3 | `=VLOOKUP($C$13,INDIRECT("'"&$C$19&"'!"&"$A$8:$E$57"),A16,FALSE)` |

4 | `=VLOOKUP($C$13,INDIRECT("'"&$C$19&"'!"&"$A$8:$E$57"),A17,FALSE)` |

### Effects of using VLOOKUP from another sheet with variable sheet name formula example

The following images illustrate the results returned by the VLookup formulas that work with data from another sheet (whose name is specified in a cell). As expected, the formulas (in cells C14 to C17) do the following:

- Look for the value (LookupValue) specified in cell C13 (Marie Kelly) in the first column (column A) of the lookup table ($A$8:$E$57) in the sheet specified in cell C19 (VLOOKUP from another sheet data).
- Return the values in the same row but from the columns specified in column A (ColumnIndexNumber), as follows:
- Cell C14: The value in the second (A14) column of the lookup table (Surface Laptop).
- Cell C15: The value in the third (A15) column of the lookup table (Chicago).
- Cell C16: The value in the fourth (A16) column of the lookup table (300).
- Cell C17: The value in the fifth (A17) column of the lookup table ($1,499).

## #11: VLOOKUP from another workbook

### VLOOKUP from another workbook formula

To **carry out a VLookup that works with data from another workbook**, use a formula with the following structure:

`=VLOOKUP(LookupValue,'WorkbookPath[WorkbookName]WorksheetName'!LookupTableAddress,ColumnIndexNumber,RangeLookup)` |

### VLOOKUP from another workbook process

To **carry out a VLookup that works with data from another workbook**, follow these steps:

- Specify the value you want to look up (LookupValue) in the first (leftmost) column of the lookup table (‘WorkbookPath[WorkbookName]WorksheetName'!LookupTableAddress).
- Identify the cell range (a table array) containing the lookup table using:
- The workbook reference and worksheet name wrapped within single quotes (‘) (‘WorkbookPath[WorkbookName]WorksheetName');
- An exclamation sign (!); and
- The address of the cell range (a table array) containing the lookup table (LookupTableAddress).

- Specify the index number of the column (within the lookup table) from which you want to obtain a value (ColumnIndexNumber).
- Specify whether VLOOKUP searches for an exact or approximate match (RangeLookup).

### VLOOKUP from another workbook formula explanation

##### Item: VLOOKUP

The VLOOKUP function does the following:

- Looks for a value (LookupValue) in the first (leftmost) column of a table (‘WorkbookPath[WorkbookName]WorksheetName'!LookupTableAddress); and
- Returns a value in the same row but from another column you specify (ColumnIndexNumber).

##### Item: LookupValue

The lookup_value argument of the VLOOKUP function (LookupValue) is the value you look up in the first (leftmost) column of the lookup table (‘WorkbookPath[WorkbookName]WorksheetName'!LookupTableAddress). In other words, LookupValue must be in the first column of the lookup table you specify with ‘WorkbookPath[WorkbookName]WorksheetName'!LookupTableAddress.

You can specify LookupValue as either:

- A value;
- A text string; or
- A cell reference.

##### Item: ‘WorkbookPath[WorkbookName]WorksheetName'!LookupTableAddress

The table_array argument of the VLOOKUP function (‘WorkbookPath[WorkbookName]WorksheetName'!LookupTableAddress) is the cell range in which VLOOKUP searches for the following:

- The LookupValue in the first column of the lookup table (‘WorkbookPath[WorkbookName]WorksheetName'!LookupTableAddress); and
- The value to return in the column you specify (ColumnIndexNumber).

When carrying out a VLookup with data from another workbook, build this table_array argument with the following items, as applicable:

- Workbook reference: How the reference to the source workbook that contains the lookup table is displayed depends on whether the workbook is open or closed.
- If the source workbook is open:
- WorkbookPath isn't required.
- WorkbookName is the name of the workbook containing the lookup table.
- WorkbookName is wrapped in square brackets ([]).

- If the source workbook is closed:
- WorkbookPath is the entire path to the source workbook containing the lookup table.
- WorkbookName is the name of the workbook containing the lookup table.
- WorkbookName is wrapped in square brackets ([]).

- If the source workbook is open:
- WorksheetName: Name of the worksheet containing the lookup table.
- Single quotes (‘): Single quotes (‘) aren't always required. They're, however, required when WorkbookName or WorksheetName include certain characters, such as spaces.
- !: When referring to a different sheet, separate the worksheet name (WorksheetName') and the actual cell address reference (LookupTableAddress) with an exclamation sign(!).
- LookupTableAddress: The address of the cell range containing the lookup table.

The lookup table you specify with ‘WorkbookPath[WorkbookName]WorksheetName'!LookupTableAddress must include both of the following columns:

- The first column, which must contain the LookupValue; and
- The column from which VLOOKUP should return a value.

##### Item: ColumnIndexNumber

The col_index_num argument of the VLOOKUP function (ColumnIndexNumber) is the column number within the lookup table (‘WorkbookPath[WorkbookName]WorksheetName'!LookupTableAddress) from which VLOOKUP returns a value, as follows:

Column | ColumnIndexNumber | Comments |

First | 1 | Must contain the LookupValue. |

Second | 2 | |

Third | 3 | |

… | … | |

#th | # |

##### Item: RangeLookup

The range_lookup argument of the VLOOKUP function (RangeLookup) specifies whether VLOOKUP searches for an approximate or an exact match for LookupValue in the first column of the lookup table (‘WorkbookPath[WorkbookName]WorksheetName'!LookupTableAddress).

- Set RangeLookup to TRUE when searching for an approximate match.
- Set RangeLookup to FALSE when searching for an exact match.

### VLOOKUP from another workbook formula example

The worksheet formulas below carry out a series of VLookups with data from another workbook as follows:

- LookupValue: The value specified in cell C6 ($C$6).
- WorkbookPath: None. The source workbook is open.
- WorkbookName: Excel VLOOKUP Function Tutorial – VLookup table in another workbook.xlsx ([Excel VLOOKUP Function Tutorial – VLookup table in another workbook.xlsx]).
- WorksheetName: VLOOKUP another workbook data (VLOOKUP another workbook data').
- LookupTableAddress: Cells A8 to E57 ($A$8:$E$57).
- ColumnIndexNumber: The column number specified in column A (A7, A8, A9 and A10).
- RangeLookup: FALSE.

No. | VLookup formula |

1 | `=VLOOKUP($C$6,'[Excel VLOOKUP Function Tutorial - VLookup table in another workbook.xlsx]VLOOKUP another workbook data'!$A$8:$E$57,A7,FALSE)` |

2 | `=VLOOKUP($C$6,'[Excel VLOOKUP Function Tutorial - VLookup table in another workbook.xlsx]VLOOKUP another workbook data'!$A$8:$E$57,A8,FALSE)` |

3 | `=VLOOKUP($C$6,'[Excel VLOOKUP Function Tutorial - VLookup table in another workbook.xlsx]VLOOKUP another workbook data'!$A$8:$E$57,A9,FALSE)` |

4 | `=VLOOKUP($C$6,'[Excel VLOOKUP Function Tutorial - VLookup table in another workbook.xlsx]VLOOKUP another workbook data'!$A$8:$E$57,A10,FALSE)` |

### Effects of using VLOOKUP from another workbook formula example

The following images illustrate the results returned by the VLookup formulas that work with data from another workbook. As expected, the formulas (in cells C7 to C10) do the following:

- Look for the value (LookupValue) specified in cell C6 (Marie Kelly) in the first column (column A) of the lookup table ($A$8:$E$57) in the “VLOOKUP another workbook data” worksheet (VLOOKUP another workbook data') in the “Excel VLOOKUP Function Tutorial – VLookup table in another workbook.xlsx” workbook ([Excel VLOOKUP Function Tutorial – VLookup table in another workbook.xlsx]).
- Return the values in the same row but from the columns specified in column A (ColumnIndexNumber), as follows:
- Cell C7: The value in the second (A7) column of the lookup table (Surface Laptop).
- Cell C8: The value in the third (A8) column of the lookup table (Chicago).
- Cell C9: The value in the fourth (A9) column of the lookup table (300).
- Cell C10: The value in the fifth (A10) column of the lookup table ($1,499).

## Learn more about working with the VLOOKUP function in Excel

### Workbook examples used in this Excel VLOOKUP Tutorial

You can **get immediate free access to the example workbooks that accompany this Excel VLOOKUP Tutorial** by subscribing to the Power Spreadsheets Newsletter.

### References to constructs used in this Excel VLOOKUP Tutorial

Use the following links to visit the appropriate webpage in the Excel help center:

- Carry out a VLookup with the VLOOKUP function.
- Concatenate one or more strings with the text concatenation (&) operator.
- Create a counter for the number of matches a criterion has appeared in a VLookup table with the COUNTIF function.
- Handle #N/A errors with the IFNA function.
- Work with wildcard characters.
- Obtain the index number of a column with a particular header with the MATCH function.
- Create dynamic references to other workbooks or worksheets with the INDIRECT function.

### Books about Excel and the Excel VLOOKUP function

Use the following links to visit the book's webpage in Amazon. PowerSpreadsheets.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.

- Excel 2016 All-in-One For Dummies, by Greg Harvey.
- Microsoft Excel 2016 Step by Step, by Curtis Frye.
- Excel 2016 Bible, by John Walkenbach.
- Excel 2016 in Depth, by Bill Jelen.
- Excel Formulas and Functions for Dummies, by Ken Bluttman.
- Excel 2016 Formulas, by Michael Alexander and Dick Kusleika.
- Excel 2016 Formulas and Functions, by Paul McFedries.