Search one row or one column (or an array) for a value

(Vector form) =LOOKUP (lookup_value, lookup_vector, [result_vector])

(Array form) =LOOKUP(lookup_value, array)

---

For Excel 365 subscribers, try using the new XLOOKUP function instead of LOOKUP. XLOOKUP works faster and in any direction (up, down, left, right).

For other versions of Excel, try using the newer vertical VLOOKUP and horizontal HLOOKUP functions.

---

LOOKUP has 2 forms: vector form and array form. Each uses different syntax.

LOOKUP (vector form) searches one row or one column for a value. It defines 'what to lookup', 'where to lookup' and 'the row or column to return the result'.

LOOKUP (array form) looks in the first row or column of an array for the lookup_value and returns a value from the same position in the last row or column of the array. It defines 'what to lookup' and 'where to lookup'. Use VLOOKUP and horizontal HLOOKUP functions instead.

---

LOOKUP and vector form.

In the example below, the LOOKUP function is used to match geographic and population data to specific countries and perform calculations on population data over time.

---

LOOKUP and array form.

In the example below, the LOOKUP function is used to match population data from differently arranged arrays.

---

Syntax (vector form)

=LOOKUP (lookup_value, lookup_vector, [result_vector])

The LOOKUP function consists of at least 2 arguments:

1. lookup_value. Required. The value to be looked for.
2. lookup_vector. Required. A range that contains only one row or one column. Values can be text, numbers, or logical values. Values must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE. LOOKUP might otherwise not return the correct value. Uppercase and lowercase text are equivalent.
3. result_vector. Optional. A range that contains only one row or column. Must be the same size as the lookup_vector.

---

Syntax (array form)

=LOOKUP(lookup_value, array)

The LOOKUP function consists of 2 arguments:

1. lookup_value. Required. The value to be looked for. Can be a number, text, a logical value, or a name or reference that refers to a value.
2. array. Required. A range of cells with text, numbers, or logical values to compare with the lookup_value.

---

Try it now!

• Enter your formulas into the grey cells.

---

Scope

LOOKUP (vector form) defines 'what to lookup', 'where to lookup' and 'the row or column to return the result'.

LOOKUP (array form) defines 'what to lookup' and 'where to lookup'.

Vector (text):	=LOOKUP("United States",B3:B12,E3:E12) || result is a value from column E which is a match to the lookup_value "United States" in column B.
Vector (number):	=LOOKUP(1.23,B3:B12,E3:E12) || result is a value from column E which is a match to the lookup_value 1.23 in column B. If there is no exact match, the nearest smaller value is returned.
Horizontal Array:	=LOOKUP("Brazil",C3:E4) || result is a value from the 2nd row in the range C3:E4 which is a match to the lookup_value "Brazil".
Vertical Array:	=LOOKUP("India",B8:C14) || result is a value from the 2nd column in the range B8:C14 which is a match to the lookup_value "India".

Caveats

• Use XLOOKUP (Excel 365 subscribers), VLOOKUP or HLOOKUP functions instead.
• LOOKUP returns approximate matches.

• lookup_value
  • If LOOKUP can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.
  • If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP returns the #N/A error value. Use the IFNA function to trap and handle #N/A errors.
• lookup_vector
  • LOOKUP assumes that lookup_vector in a vector form is in ascending order.
  • lookup_vector must be the same size as result_vector.
• result_vector
  • result_vector must be the same size as lookup_vector.
  • If result_vector is not provided, LOOKUP returns the value of the match found in lookup_vector.
  • When LOOKUP can't find a match, it will match the next smallest value.
• array
  • If LOOKUP can't find the value of lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.
  • If the lookup_value is smaller than the smallest value in the first row or column of the array, LOOKUP returns the #N/A error value.
  • If array is wider (more columns) than it is tall (rows), LOOKUP searches for the lookup_value in the first row.
  • If array is taller (more rows) than it is wide (columns), LOOKUP searches for the lookup_value in the first column.
  • Values must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE. LOOKUP might otherwise not return the correct value.

---

Common issues

• #N/A: lookup_value is likely smaller than the smallest value in the first row or column of the array.

---

Related Functions

• XLOOKUP to lookup values in any direction.
• VLOOKUP to lookup values by row in a vertical table or range.
• HLOOKUP to lookup values by column in a horizontal table or range.
• INDEX and MATCH to lookup values in any direction.
• OFFSET to lookup values in any direction.

---

Functions Category

• Lookup & Reference

 

---