Lookup functions pull data from a reference table: the price for an item code, the name for an employee ID.
VLOOKUP(value, table, col_index, FALSE) | searches the first column of the table and returns the value from the given column. FALSE means exact match |
XLOOKUP(value, lookup_range, return_range, [if_not_found]) | modern replacement for VLOOKUP (Excel 2021/365): searches any column, exact match by default, your own text instead of #N/A |
MATCH(value, range, 0) | the position of a value in a range |
INDEX(range, position) | the value at a position. INDEX + MATCH works in every Excel version |
LARGE(range, k) | the k-th largest value (for a "top 3"); its pair is SMALL |
Lock the VLOOKUP table with $ signs, otherwise it shifts when you copy the formula down.