Skip to content
Google Sheets

LOOKUP function in Google Sheets

Looks through a row or column for a key and returns the value of the cell in a result range located in the same position as the search row or column.

=LOOKUP(search_key, search_range|search_result_array, [result_range])

LOOKUP syntax and parameters

Three arguments, two required.

  • search_keyanyRequired

    The value to search for in the row or column. For example, 42, "Cats", or I24.

  • search_range|search_result_arrayanyRequired

    One method of using LOOKUP is to provide a single row or column search_range to look through for the search with a second argument result_range. The other way is to combine these two arguments into one search_result_array where the first row or column is searched and a value is returned from the last row or column in the array.

  • result_rangerangeOptional

    [ OPTIONAL ] - The range from which to return a result. The value returned corresponds to the location where search_key is found in search_range. This range must be only a single row or column and should not be used if using the search_result_array method.

LOOKUP examples

Formulas you'll actually reuse.

  1. A loyalty tier from spend — the thresholds must be sorted ascending:

    =LOOKUP(B2, {0, 1000, 5000, 20000}, {"Bronze", "Silver", "Gold", "Platinum"})

    Result"Gold"

  2. The last non-empty entry in a column — the classic LOOKUP trick:

    =LOOKUP(2, 1/(A2:A200 <> ""), A2:A200)

    Result"Acme Corp"

LOOKUP in Excel

Same name — your formula ports as-is.

Try LOOKUP in the playground

Edit the example — nothing to install.

Preloaded with the LOOKUP formula from Example 1 — change anything and watch it respond.

Loading the editor…

LOOKUP errors

What they mean — and the fixes.

  • #N/A

    The value is smaller than every entry in the lookup vector — LOOKUP returns the largest entry at or below it, so start the vector with a floor such as 0.

Related functions

More ways Google Sheets gets this done.