A teacher has created a spreadsheet of test marks of five students and written the formula =HLOOKUP(B4, $D$1:$K$2, 2) in cell C4, then copied it from C4 to the range C5:C8. What values will be returned by the formulae in the range C4:C8?

HLOOKUP searches the top row of the fixed table for the value in the lookup cell and returns the entry directly below it from the row given by the index number 2.

The lookup cell B4 is a relative reference, so copying the formula down changes it to B5, B6, B7 and B8 in turn.

The table range is written with dollar signs as an absolute reference, so it stays fixed at the same block for every copied formula.

Each mark in B4 to B8 is matched against the top row and the value beneath it is returned, giving the sequence B, A, C, E and G.

An absolute reference locks a cell address, while a relative reference adjusts as the formula moves.

HLOOKUP searches horizontally across a row, whereas VLOOKUP searches vertically down a column.