Lookup & Reference Functions

INDEX grabs a value at a specific row/column position in a range. Think of it as 'go to row 3, column 2, and return whatever's there.' Combined with MATCH, it becomes a lookup that can search in any direction.

Turns text that looks like a cell reference into an actual reference.

MATCH finds where a value sits in a range and returns its position number. It's the 'finder' half of INDEX/MATCH — MATCH locates the row, INDEX retrieves the value at that row.

Returns a range shifted by a given number of rows and columns from a starting point.

VLOOKUP finds a value in the leftmost column of a range and returns the corresponding value from any column to the right. Think of it as a vertical search — it scans down a column, finds your match, then pulls data from across that row.

XLOOKUP is the upgraded VLOOKUP — it can search left or right, doesn't require a column index number, handles missing values with a default result, and supports nested arrays. Available in Excel 365 and Excel 2021+.