Problem | Printable page |

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.

Experimental probability. Summation of series. Random variables. Probability distributions, expectation and variance. Approximate solution by numerical methods.. Mathematical modelling. Tree diagrams. Theoretical probability. Conditional probability. Spreadsheets.