Question

Explain Array form of LOOKUP function.

Hint:

Define the array form, give the syntax LOOKUP (lookup_value, array), then explain lookup_value, the nearest smaller match with #N/A, and row or column search.

Answer 1

Step 1: What the array form does:
LOOKUP is a spreadsheet function that finds a value and returns a related value.
It has two forms, vector form and array form. We explain the array form.
The array form looks in the first row or column of an array for the given value. It then returns the value from the same position in the last row or column of the array. (1.5 marks)

Step 2: Syntax:
The syntax is LOOKUP (lookup_value, array).
Here lookup_value is the item to be searched and array is the block of cells to be searched.

Step 3: Meaning of lookup_value:
lookup_value is the value that LOOKUP searches for in the array.
It can be a number, text, a logical value or a name. (0.5 mark)

Step 4: When the value is not found:
If LOOKUP cannot find lookup_value, it uses the largest value in the array that is less than or equal to lookup_value. (0.5 mark)
If lookup_value is smaller than the smallest value in the first row or column, it returns the #N/A error value. (0.5 mark)

Step 5: Where it searches:
If the array is wider than it is tall (more columns than rows), LOOKUP searches the first row. (0.5 mark)
If the array is square or taller than it is wide, it searches the first column. (0.5 mark)

Final Answer:
Array form of LOOKUP searches the first row or column of an array and returns the value from the same position in the last row or column. Syntax: LOOKUP (lookup_value, array). 4 marks.