Question

Explain Array form of LOOKUP function.

Answer 1

The array form of LOOKUP looks for lookup_value in the first row or first column of a given array and returns the entry from the same location in the last row or column.

Syntax: LOOKUP(lookup_value, array). The function compares lookup_value to the values in the array's first row (if the array is wider than tall) or first column (if the array is taller than wide); when it finds the position, it returns the value from the last row or column in that position. Because the match is approximate (using nearest match behaviour), the lookup range should be sorted in ascending order to ensure reliable results.

Example: Given array A1:C2 where A1:C1 = {10, 20, 30} and A2:C2 = {“Ten”,“Twenty”,“Thirty”}, the formula = LOOKUP(20, A1:C2) searches the first row, finds 20 in column 2, and returns “Twenty” from the last row (A2:C2).

The array form is less flexible (it requires sorted data and only returns values from the last row/column); Microsoft prefers VLOOKUP/HLOOKUP (or the current XLOOKUP) for clearer, safer lookup behaviour.