Lookup functions

Lookup functions let the sheet find a value in a table for you.

LOOKUP (vector form) searches one row or column, which must be in ascending order, and returns the value in the same position of a result row or column. If it cannot find an exact match, it takes the largest value less than or equal to what you asked for; below the smallest value it gives #N/A. Looking up 7.66 among 4.14, 4.19, 5.17, 5.77 and 6.39 matches 6.39 (and returns its colour, blue). The array form searches the first row or column of a table and returns from the last.

VLOOKUP (V for vertical) searches down the first column of a table and returns a value from the column you name:

• VLOOKUP(lookup_value, table_array, col_index_num, range_lookup).
• =VLOOKUP(A3, A1:D7, 4, FALSE) finds the employee in A3 and returns column 4, the basic pay.
• range_lookup FALSE: an exact match only (#N/A if none). TRUE or left out: an approximate match, and the first column must be sorted.
• col_index_num below 1 gives #VALUE!; more than the table's columns gives #REF!.

HLOOKUP (H for horizontal) does the same across the first row, returning a value from a row you name, for example one quarter's budget taken from another sheet.

Write the table array with absolute references, so it does not slide when the formula is copied.