|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

VLOOKUP

VLOOKUP

  • Last updated on

Description

Use this function to search for a value in the first column of a table range and return a value in the same row from another specified column. Supported in Chains.

VLOOKUP is useful for finding information in large datasets, such as looking up prices in a product catalog or finding employee details in a database.

 

Syntax

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

 

Inputs

This function accepts the following arguments:

Name Required Description Valid input
lookup_value Yes The value to search for in the first column of the table array. A cell reference, a cell range, a number, a text string, or a formula which results in any of these. 
table_array Yes The table of information in which data is looked up. A cell range.
col_index_num Yes The column number in table_array from which the matching value should be returned. A positive integer, a cell reference containing a positive integer, or a formula which results in either of these. 1 = the first column in table_array.
range_lookup No Specifies whether you want VLOOKUP to find an exact match or an approximate match. TRUE (default) for approximate match, FALSE for exact match. 

 

Example

Sample data

  A B C D
1 Product ID Product Name Category Price
2 1001 Widget A Gadgets $10.99
3 1002 Widget B Gadgets $15.99
4 1003 Gizmo X Electronics $25.50
5 1004 Gizmo Y Electronics $30.00
6 1005 Gizmo Z Electronics $37.50

Sample formulas

Use case Formula Explanation and Result
Look up a product ID to find its corresponding product name with an exact match. =VLOOKUP(1002, A2:D6, 2, FALSE)

This formula works in the following manner:

  • VLOOKUP(: This is the function itself.
  • 1002: The lookup_value.
  • A2:D6: The table_array. The function searches for the `lookup_value` in the first column of this range (Column A).
  • 2: The col_index_num. The formula will return the value from the 2nd column of the array (Column B).
  • FALSE: The range_lookup, specifying an exact match.
  • The formula finds 1002 in A3 and returns the value from the 2nd column of that row (B3).

For this data set, the formula returns Widget B.
Look up a product ID to find its corresponding price with an exact match. =VLOOKUP(1003, A2:D6, 4, FALSE)

This formula works in the following manner:

  • VLOOKUP(: This is the function itself.
  • 1003: The lookup_value.
  • A2:D6: The table_array. The function searches for 1003 in Column A.
  • 4: The col_index_num. The formula will return the value from the 4th column of the array (Column D).
  • FALSE: Specifies an exact match.
  • The formula finds 1003 in A4 and returns the value from the 4th column of that row (D4).

For this data set, the formula returns $25.50.
Look up a product name to find its corresponding category with an exact match. =VLOOKUP("Gizmo X", B2:D6, 2, FALSE)

This formula works in the following manner:

  • VLOOKUP(: This is the function itself.
  • "Gizmo X": The lookup_value.
  • B2:D6: The table_array. The function searches in the first column of this range, which is Column B.
  • 2: The col_index_num. The formula will return the value from the 2nd column of the specified range (which is Column C).
  • FALSE: Specifies an exact match.
  • The formula finds "Gizmo X" in B4 and returns the value from the 2nd column of the range B2:D6 in that row (C4).

For this data set, the formula returns Electronics.
Look up an approximate value and return the value from the corresponding row. =VLOOKUP(1006, A2:D6, 2, TRUE)

This formula works in the following manner:

  • VLOOKUP(: This is the function itself.
  • 1006: The lookup_value.
  • A2:D6: The table_array.
  • 2: The col_index_num (Column B).
  • TRUE: Specifies an approximate match. This requires the first column (A) to be sorted in ascending order.
  • The formula searches for the largest value in Column A that is less than or equal to 1006. This is 1005 (in A6).
  • It returns the value from the 2nd column of that row (B6).

For this data set, the formula returns Gizmo Z.
Look up a product ID with the range_lookup argument omitted, which defaults to an approximate match. =VLOOKUP(1002, A2:D6, 2)

This formula works in the following manner:

  • VLOOKUP(: This is the function itself.
  • 1002: The lookup_value.
  • A2:D6: The table_array.
  • 2: The col_index_num (Column B).
  • The range_lookup argument is omitted, defaulting to TRUE (approximate match).
  • The function finds an exact match for 1002 in A3, so it returns the value from B3.

For this data set, the formula returns Widget B.

 

Notes

  • The lookup value must be in the first column of the table_array for VLOOKUP to work correctly.
  • If range_lookup is TRUE or omitted, the first column of table_array must be sorted in ascending order.
  • If range_lookup is FALSE and the lookup_value is not found, VLOOKUP returns #N/A error.
  • VLOOKUP is not case-sensitive when comparing text values.
  • For better performance with large datasets, consider using INDEX and MATCH functions instead of VLOOKUP.

 

Tips

  • Use FALSE for range_lookup when you need an exact match, especially with unsorted data or text values.
  • Combine VLOOKUP with other functions such as IFERROR to handle cases where the lookup value is not found.
  • For reverse lookups or when your lookup column is not the leftmost, consider using INDEX and MATCH instead.
  • When working with large datasets, using VLOOKUP with range_lookup set to TRUE can improve performance if your data is sorted.
  • Remember that VLOOKUP can only return values from columns to the right of the lookup column in the table_array.

 

Related functions

CELL
CHILDREFS
HLOOKUP
INDEX
MATCH
VLOOKUP
XLOOKUP
XMATCH

Was this article helpful?
0 out of 0 found this helpful
Description Use this function to search for a value in the first column of a table range and return a value in the sa...