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.
Examples
This example references the following sheet information:
| Clothing Item | Item Number | Price | Stock | |
| 1 | T-Shirt | C001 | 15.00 | 78 |
| 2 | Pants | C002 | 35.50 | 42 |
| 3 | Jacket | C003 | 50.75 | 16 |
Given the table above, here are some examples of using VLOOKUP in a sheet:
| Formula | Description | Result |
| =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 |