Lookup & Reference Functions

Returns the nth value from a list.

Looks up a value across rows (horizontal search).

INDEX retrieves a value from a specific row and column in a range.

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

MATCH finds the position of a value in a range.

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

Flips rows and columns of an array.

VLOOKUP searches for a value in the first column of a table array and returns a matching value from another column in the same 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+.