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 |