Excel Technique: LOOKUP Functions

These notes explain a particular use of spreadsheets: looking up results which have already been calculated. There is quite a collection of Lookup functions available, but VLOOKUP (used in the Happy Numbers investigation) is the main topic here.

VLOOKUP: Looks down the first column of a block (range) for a specified value, then returns a value further along that same row.

For example:

= VLOOKUP (63, A1: E11, 5)

would look down the first column of the range A1: E11 until 63 was located, then return the value in that same row but in the fifth column.

The V of VLOOKUP stands for vertical. HLOOKUP performs the same process horizontally. HLOOKUP finds the position of a given value in the first row then, from the same column as the located value, returns the corresponding value from a specified lower row.

Two more Lookup functions deserve a brief mention:

INDEX and MATCH INDEX returns the value at a specific position in a given range.

= INDEX ( B6 : D10, 3, 2)
would return the value in the third row and second column of the range B6 : D10. This can be useful when the row and column values are variables.

MATCH
operates on a single row or on a single column. It returns the values position along that row or column.

= MATCH ( 63, A2 : A11, 0 )
would report that 63 was the seventh item in that line of values, A2 : A11 The 0 following the range reference, causes only an exact match to be reported.