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.
- 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.
Examples
This example references the following sheet information:
| Clothing Item | Units Sold | Item Number | Price Per Unit | In Stock? | Status | |
|---|---|---|---|---|---|---|
| 1 | T-Shirt | 78 | C001 | 15.00 | true | Green |
| 2 | Pants | 42 | C002 | 35.50 | false | Red |
| 3 | Jacket | 217 | C003 | 200.00 | true | Yellow |
Given the table above, here are some examples of using VLOOKUP in a sheet:
| Formula | Description | Result |
|---|---|---|
| =VLOOKUP("T-Shirt", [Clothing Item]1:Status3, 3, false) | Return the Item Number of "T-Shirt" | C001 |
| =IF([In Stock?]1 = 1, VLOOKUP(“T-Shirt”, [Clothing Item]1:Status3, 6)) | 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, VLOOKUP([Clothing Item]2, [Clothing Item]1:[In Stock?]3, 1)) | 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 1. | Pants |
| =VLOOKUP(“Jacket”, [Clothing Item]2:[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.00). Then multiple this by the “Units Sold” column value (217). | 43400.00 |