Excel LOOKUP function

Summary

The LOOKUP function in Excel is used for searching one row or one column for a value and retrieving a corresponding value from another row or column. It’s a basic lookup function that can be utilized for simpler lookup tasks compared to VLOOKUP or HLOOKUP. LOOKUP function comes in two forms: vector and array.
Syntax
				
					=LOOKUP(lookup_value, lookup_vector, [result_vector]) //vector form
=LOOKUP(lookup_value, array) //array form
				
			
  • lookup_value: The value to search for.
  • lookup_vector: The range containing the value to look for.
  • [result_vector]: [Optional] The range containing the result to return.
  • array: A two-row or two-column range of cells containing pairs of lookup values and result values.
Return value

Returns the value found in the result_vector or array that corresponds to the lookup_value.

How to use

The vector form is used when you have separate ranges for the lookup values and the result values. The array form is used when the data is in a single range that contains both lookup and result values.

Examples

LOOKUP in Vector Form
Basic One-Dimensional Lookup: Finding a corresponding value in a separate range:
				
					=LOOKUP("Apple", A2:A5, B2:B5)
				
			

This formula searches for “Apple” in the range A2:A5 and returns the corresponding value from the range B2:B5.

LOOKUP in Array Form
Two-Dimensional Data Lookup: Retrieving a result from a two-row array:
				
					=LOOKUP(3, A1:B2)
				
			
Assuming the first row contains lookup values and the second row contains results, this formula finds the number 3 in the first row and returns the corresponding value from the second row.
LOOKUP for Approximate Match
Approximate Match in a Sorted List: Searching for the nearest lower value in a sorted list:
				
					=LOOKUP(250, A2:A10, B2:B10)
				
			
This formula looks for the number 250 in a sorted range A2:A10 and returns the corresponding value from B2:B10 that’s closest to and less than 250.
LOOKUP with Unsorted Data
Lookup in Unsorted Data: Using LOOKUP when the data isn’t sorted:
				
					=LOOKUP("Orange", CHOOSE({1,2}, A2:A5, B2:B5))
				
			
For unsorted data, the CHOOSE function can be used to create an array for LOOKUP to search through.
LOOKUP for Last Non-Empty Cell
Finding the Last Non-Empty Cell in a Column: To find the value in the last non-empty cell in a column:
				
					=LOOKUP(2, 1/(A:A<>""), A:A)
				
			
This formula uses an array formula technique to find the last non-empty cell in column A.

Additional Notes

  • LOOKUP requires that the lookup_vector or the first row/column of array be sorted in ascending order for an approximate match.
  • If lookup_value is smaller than the smallest value in the lookup_vector or array, LOOKUP returns the #N/A error.
  • LOOKUP is less flexible than VLOOKUP or HLOOKUP and is best suited for straightforward, one-dimensional lookups in sorted data.

Related Functions

Excel XMATCH function

The Excel XMATCH function returns the position of a value within a range, offering advanced matching and search capabilities.

Excel MATCH function

The Excel MATCH function finds the position of a value within a range, essential for dynamic lookups and complex searches.

Excel XLOOKUP function

The Excel XLOOKUP function searches for a value, offering flexible matching options and improvements over VLOOKUP and HLOOKUP.

Excel FILTER function

The Excel FILTER function dynamically isolates data in an array based on criteria, essential for targeted analysis and reporting.

Excel INDEX function

The Excel INDEX function retrieves values at a specific row and column in a range, key for dynamic data lookups and retrieval.

Excel HLOOKUP function

The Excel HLOOKUP function searches horizontally in the top row, returning values from a specified row in the same column.

Content Navigation