VLOOKUP Function

Looks up a value and returns a corresponding value in the same row but from a different column.
Sample Usage
VLOOKUP("Task E", [Task Name]1:Done5, 2, false)
Syntax
VLOOKUP(
  • search_value
  • lookup_table
  • column_num
  • [
    match_type
    ]
)
  • search_value
    The value to search for, which must be in the first column of lookup_table.
  • lookup_table
    The cell range in which to search, containing both the search_value (in the leftmost column) and the return value.
  • column_num
    A number representing the column position (in lookup_table) of the value to return, with the leftmost column of lookup_table at position 1.
  • match_type
    —[optional]
    The default is true. Specifies whether to find an exact match (false) or an approximate match (true).
Usage Notes
  • You can use VLOOKUP to look up a value from a table in another sheet. For details on referencing cells from other sheets, see Formulas: Reference Data from Other Sheets.
  • If VLOOKUP doesn't find a result, you'll receive a #NO MATCH error. Details on troubleshooting this can be found in the Formula Error Messages article.
  • The search_value must be in the leftmost column (position 1) of lookup_table.
  • To look up text strings, you must enclose the lookup value in quotation marks (for example, “Task E”).
  • With the match_type argument:
    • Set match_type to false if your lookup_table is not sorted.
    • True (the default value) assumes that the range is sorted ascending and returns the nearest match that is less than or equal to ( <= ) search_value.
    • False returns the first exact match.
  • You can insert the column number into a formula to indicate which column you are retrieving the value from.
     
    For example, the formula =VLOOKUP([Inventory Item]3, [Inventory Item]1:[Number of Items in Stock]4, 3, false) will return the value 4. The formula is written to retrieve a value from the third column (Number of Items in Stock) in the table below.

    vlookup example table
Examples

This example references the following sheet information:

 Clothing ItemItem NumberPriceStock
1T-ShirtC00115.0078
2PantsC00235.5042
3JacketC00350.7516

Given the table above, here are some examples of using VLOOKUP in a sheet:

FormulaDescriptionResult
=VLOOKUP("T-Shirt", [Clothing Item]1:Stock3, 2, false)Return the Item Number of "T-Shirt"C001
=VLOOKUP("Pants", [Clothing Item]1:Stock3, 3, false)Return the Price of "Pants"35.50
=VLOOKUP("Jacket", [Clothing Item]1:Stock3, 4, false)Return the Stock of "Jacket"16
Was this article helpful?
YesNo