LOOKUP - Array

What Does It Do?

This function looks for a piece of information in a list, and then picks an item from the last cell in the adjacent row or column.

It always picks the data from the end of the row or column, so it is no good if you need to pick data from part way across a list, (use VLOOKUP or HLOOKUP).

The way in which the function decides whether to pick from the row or column is based on the size of the table.

Syntax

=LOOKUP(WhatToLookFor,RangeToLookIn)

The WhatToLookFor should be a single item.

The RangeToLook in can be either horizontal or vertical.

Be careful not to include unnecessary heading in the range as these will cause errors.

Formatting

No special formatting is needed.

Problem

The list of information to be looked through must be sorted in ascending order, otherwise errors will occur, either as #N/A or incorrect results.

Table 1 shows the Name column sorted alphabetically, the results of using =LOOKUP() will be correct.

Table 2 shows the same data, but not sorted. Sometimes the results will be correct, but other times the result will be an #N/A error or incorrect figure.