Lookup and reference functions
Lookup functions find a value in one place and bring back a matching value from somewhere else. They are the glue between tables, and the first thing most job tests check.
43 functions in this category.
Start here
- VLOOKUP
The classic lookup: find an item in the first column and fetch something from the same row. Common in interviews, though XLOOKUP is the modern choice.
- XLOOKUP
The modern lookup: find a value in one range and return the matching entry from another, in any direction. It replaces most VLOOKUP and INDEX/MATCH uses.
- MATCH
Finds where an item sits in a list and returns its position number. Usually combined with INDEX.
- HLOOKUP
Searches across the top row for a match and returns a value from a row below it. It is the sideways twin of VLOOKUP, now mostly replaced by XLOOKUP.
- FILTER
Returns only the rows that satisfy one or more conditions, spilling the results. It replaces many manual filter and lookup-all tricks.
- INDEX
Returns the item at a given row and column of a range. Paired with MATCH it makes a flexible lookup that works in any direction.
All lookup and reference functions
| Function | What it does | Level | Since | Spills? |
|---|---|---|---|---|
ADDRESS | Builds a cell reference as text from row and column numbers. Often combined with INDIRECT, or to show where a MATCH landed. | Intermediate | 2007 or earlier | No |
AREAS | Counts how many separate ranges are in a multi-area reference. Rarely used outside advanced reference work. | Expert | 2007 or earlier | No |
CHOOSE | Picks one value from a list by number. Handy for turning a code into a label, or for choosing between ranges. | Intermediate | 2007 or earlier | No |
CHOOSECOLS | Pulls specific columns out of an array in any order, including repeats. A formula-based alternative to hiding columns. | Intermediate | 2024 | Yes |
CHOOSEROWS | Returns specific rows of an array in the order you list them, such as the first and last rows. | Intermediate | 2024 | Yes |
COLUMN | Returns the column number, with A as 1. Used to build offsets, count across, or feed INDEX and VLOOKUP's column argument. | Intermediate | 2007 or earlier | No |
COLUMNS | Counts columns in a range or array, useful for sizing or checking spill output. | Intermediate | 2007 or earlier | No |
DROP | Removes rows or columns from the start or end of an array. A neat way to strip a header row from a range. | Intermediate | 2024 | Yes |
EXPAND | Makes an array larger by padding with a chosen value, useful before stacking arrays of different sizes. | Advanced | 2024 | Yes |
FIELDVALUE | Extracts a single property from a linked data type cell (Stocks, Geography and similar) as a regular value. Mostly used when you want formulas rather than the dot notation. | Expert | 365 only | No |
FILTER | Returns only the rows that satisfy one or more conditions, spilling the results. It replaces many manual filter and lookup-all tricks. | Beginner | 2021 | Yes |
FORMULATEXT | Shows another cell's formula as text. Useful for documentation, audits and teaching sheets. | Intermediate | 2013 | No |
GETPIVOTDATA | Reads a specific total from a PivotTable so it stays correct when the pivot is rearranged. Excel makes it automatically when you click a pivot cell from a formula. | Advanced | 2007 or earlier | No |
GROUPBY | Summarises data by one or more fields with a single formula, like a PivotTable that recalculates live. Good for quick totals by category. | Advanced | 365 only | Yes |
HLOOKUP | Searches across the top row for a match and returns a value from a row below it. It is the sideways twin of VLOOKUP, now mostly replaced by XLOOKUP. | Intermediate | 2007 or earlier | No |
HSTACK | Appends arrays horizontally into one, handy for combining ranges from different places into one table. | Intermediate | 2024 | Yes |
HYPERLINK | Makes a clickable link from a formula, so you can build links dynamically, for example from an ID column. | Intermediate | 2007 or earlier | No |
IMAGE | Pulls an image from a web address into a cell so it moves and sorts with the data, like product thumbnails. | Intermediate | 365 only | No |
IMPORTCSV | Loads a CSV file into the sheet with a formula, refreshing when recalculated. A lightweight alternative to Power Query for simple files. | Advanced | 365 only | Yes |
IMPORTTEXT | Reads a delimited text file straight into the grid with a formula, so the data refreshes without Power Query. | Advanced | 365 only | Yes |
INDEX | Returns the item at a given row and column of a range. Paired with MATCH it makes a flexible lookup that works in any direction. | Beginner | 2007 or earlier | No |
INDIRECT | Turns text into a live reference, so you can build sheet or cell addresses dynamically, for example a dropdown that switches sheets. | Advanced | 2007 or earlier | No |
LOOKUP | An older lookup that always does an approximate match on sorted data. Still used for the classic "last value in a column" trick. | Intermediate | 2007 or earlier | No |
MATCH | Finds where an item sits in a list and returns its position number. Usually combined with INDEX. | Beginner | 2007 or earlier | No |
OFFSET | Builds a reference a set distance from a starting cell, optionally resized. Used in dynamic ranges, though INDEX is usually preferred. | Advanced | 2007 or earlier | No |
PIVOTBY | Builds a PivotTable-style grid with a formula, grouping down rows and across columns, and recalculates when data changes. | Advanced | 365 only | Yes |
ROW | Returns the row number of a cell, handy for numbering lists or building offsets. | Intermediate | 2007 or earlier | No |
ROWS | Counts the rows in a range or array. Useful with spilled results. | Intermediate | 2007 or earlier | No |
RTD | Connects a cell to a COM real-time data feed, such as market data from a provider's add-in. Rarely seen outside finance. | Expert | 2007 or earlier | No |
SORT | Returns a sorted copy of a range that updates when the data changes, without touching the original. | Beginner | 2021 | Yes |
SORTBY | Sorts one range by values in another, even if the key column is not in the output, and supports multiple keys. | Intermediate | 2021 | Yes |
TAKE | Returns the first or last rows or columns of an array, such as the top 10 after sorting. | Intermediate | 2024 | Yes |
TOCOL | Flattens a block of cells into one column, helpful for unpivoting or feeding UNIQUE and COUNTIF. | Intermediate | 2024 | Yes |
TOROW | Flattens an array into one row, the sideways version of TOCOL. | Intermediate | 2024 | Yes |
TRANSPOSE | Turns rows into columns and vice versa with a live formula. Paste Special Transpose is the static alternative. | Intermediate | 2007 or earlier | Yes |
TRIMRANGE | Cuts blank rows and columns from the edges of a range so whole-column references like A:C behave like exactly-sized ranges. | Advanced | 365 only | Yes |
UNIQUE | Returns a list with duplicates removed. A quick way to get a list of categories, customers or IDs. | Beginner | 2021 | Yes |
VLOOKUP | The classic lookup: find an item in the first column and fetch something from the same row. Common in interviews, though XLOOKUP is the modern choice. | Beginner | 2007 or earlier | No |
VSTACK | Appends arrays one under another, such as combining monthly tables into a single list. | Intermediate | 2024 | Yes |
WRAPCOLS | Reshapes a list into columns of a fixed height, filling top to bottom then left to right. | Intermediate | 2024 | Yes |
WRAPROWS | Reshapes a list into rows of a fixed width, filling left to right then down. | Intermediate | 2024 | Yes |
XLOOKUP | The modern lookup: find a value in one range and return the matching entry from another, in any direction. It replaces most VLOOKUP and INDEX/MATCH uses. | Beginner | 2021 | Yes |
XMATCH | An upgraded MATCH with exact match as the default and options for search direction and wildcards. | Intermediate | 2021 | No |
Other categories
- Math and trig functions
80 functions
- Logical functions
19 functions
- Text functions
48 functions
- Date and time functions
25 functions
- Statistical functions
111 functions
- Financial functions
56 functions
- Information functions
21 functions
- Database functions
12 functions
- Engineering functions
54 functions
- Web functions
3 functions
- Compatibility functions
41 functions
- Cube functions
7 functions
- Add-in and automation functions
5 functions