1. Spreadsheet.com Support
  2. Functions
  3. Lookup functions

VLOOKUP

Vertical lookup. Searches the first column of a range for a key and returns the value of a specified column in the matching row.

Sample Usage

VLOOKUP(500, A2:B26, 2, FALSE)

Syntax

VLOOKUP(value, range, index, [sorted])

  • value  - The value you want to look up. 
  • range  - The range where the lookup value is located. 
  • index  - The column number in the range that contains the return value. 
  • sorted  - [OPTIONAL] A logical value that indicates if the column to be looked up is sorted. 

 

Examples

VLOOKUP(500, A2:B26, 2, FALSE) looks for an exact match of the number 500  in the range A2:B26 . If an exact match is found, the function returns the value corresponding to column 2  of the matched row.

 

Notes

  • If index is not between 1 and the number of columns in  range, #VALUE!  is returned.
  • It is recommended to set sorted to FALSE. If set to FALSE, an exact match is returned. If there are multiple matching values, the content of the cell corresponding to the first value found is returned, and #N/A is returned if no such value is found.
  • If sorted is TRUE or omitted, the nearest match (less than or equal to the search key) is returned. If all values in the search column are greater than the search key, #N/A is returned.
  • If sorted is set to TRUE or omitted, and the first column of the range is not in sorted order, an incorrect value might be returned.
  • When searching for numeric or date values, make sure that the first column in the range is not sorted by text values. For example, correctly sorted numbers should appear as (1, 2, 10, 100) rather than (1, 10, 100, 2) as they would be if they were sorted as strings. Using an incorrect sort type may cause incorrect values to be returned.
  • VLOOKUP has much better performance with sorted ranges and sorted set to TRUE. Consider sorting the column being searched.
  • You can also find matches using pattern strings that include wildcards. Currently, the use of asterisk (*) is supported.
    • An asterisk matches any sequence of characters.
    • To match an actual asterisk, type a tilde (~) before the character. For example: (~*).