Practice free

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

All lookup and reference functions

FunctionWhat it doesLevelSinceSpills?
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