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.