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

Place a VLOOKUP formula in a Contact List field to automatically assign people to tasks based on criteria in your sheet. (For example, based on department or role.)

  • 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.
  • You'll receive a #NO MATCH error if there is not a number within the range that is greater than or equal to the search_value.
  • 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 Item

Units Sold

Price Per Unit

In Stock?

Status

Assigned To

1

T-Shirt

78

$15.00

true

Green

sally@domain.com

2

Pants

42

$35.50

false

Red

tim@domain.com

3

Jacket

217

$200.00

true

Yellow

corey@domain.com


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

 

Formula

Description

Result

IF([In Stock?]1 = 1 (true), VLOOKUP(“T-Shirt”, [Clothing Item]1:Status3, 4))

Return the Status Color. If the “In Stock” column equals 1 (true) look up the value “T-Shirt” in the “Clothing Item” column and produce the value of the “Status Column”, (fourth column in the table). 

Green

IF([In Stock?]2 = 0 (false), VLOOKUP([Row #]1, [Row #]1:[In Stock?]3, 2))

Return Item Out of Stock. If the “In Stock” column equals 0 (false) look up the value of Row 2 and produce the value of the Clothing Item, column 2

Pants

VLOOKUP(“Jacket”, [Clothing Item]1:[Price Per Unit]3, 5, false) * [Units Sold]3

Return total revenue. Look up the value “Jacket” in the “Clothing Item” column, if found, produce the value in the “Price Per Unit” column ($200). Then multiple this by the “Units Sold” column value (217).

43400

VLOOKUP([Clothing Item]1, {Range on Reference Sheet}, 2, false)

Return the assigned to contact email. Look up the value in the “Clothing Item” column row 1 on the reference sheet, if found, produce, produce the value in the Assigned To column (column 2) on the reference sheet.

sally@domain.com


The final formula is an Advanced Cross Sheet formula outlining how to auto assign contacts from another sheet. Cross Sheet formulas are further outlined here: https://help.smartsheet.com/learning-track/smartsheet-advanced/advanced-cross-sheet-formulas