ADDRESS
Returns a reference as text to a single cell in a worksheet.
Lookup & ReferenceLookup & Matching Essentials
Lookup & Reference
Lookup & Reference functions allow you to search for values and return matching results from other tables.
They are one of the most frequently used function groups in real-world Excel workbooks.
Modern Excel offers improved lookup functions, but classic patterns are still widely used.
Tips
- Prefer exact matches unless approximate matching is required.
- Use clear key columns to keep lookups stable.
Excel functions in this category (40)
AREAS
Returns the number of areas in a reference.
Lookup & ReferenceLookup & Matching Essentials
CHOOSE
Chooses a value from a list of values.
Lookup & ReferenceLookup & Matching Essentials
CHOOSECOLS
Returns the specified columns from an array.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
CHOOSEROWS
Returns the specified rows from an array.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
COLUMN
Returns the column number of a reference.
Lookup & ReferenceLookup & Matching Essentials
COLUMNS
Returns the number of columns in a reference.
Lookup & ReferenceLookup & Matching Essentials
DROP
Excludes a specified number of rows or columns from the start or end of an array.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
EXPAND
Expands or pads an array to specified row and column dimensions.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
FILTER
Filters a range of data based on criteria you define.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
FORMULATEXT
Returns the formula at the given reference as text.
Lookup & ReferenceLookup & Matching Essentials
GETPIVOTDATA
Returns data stored in a PivotTable report.
Lookup & ReferenceLookup & Matching Essentials
GROUPBY
Helps a user group, aggregate, sort, and filter data based on the fields you specify.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
HLOOKUP
Looks in the top row of an array and returns the value of the indicated cell.
Lookup & ReferenceLookup & Matching Essentials
HSTACK
Appends arrays horizontally and in sequence to return a larger array.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
HYPERLINK
Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet.
Lookup & ReferenceLookup & Matching Essentials
IMAGE
Returns an image from a given source.
Lookup & ReferenceLookup & Matching EssentialsModern Excel Functions
INDEX
Uses an index to choose a value from a reference or array.
Lookup & ReferenceBeginner EssentialsLookup & Matching EssentialsMost Used
INDIRECT
Returns a reference indicated by a text value.
Lookup & ReferenceLookup & Matching Essentials
LOOKUP
Looks up values in a vector or array.
Lookup & ReferenceLookup & Matching Essentials
MATCH
Looks up values in a reference or array.
Lookup & ReferenceBeginner EssentialsLookup & Matching EssentialsMost Used
OFFSET
Returns a reference offset from a given reference.
Lookup & ReferenceLookup & Matching Essentials
PIVOTBY
Helps a user group, aggregate, sort, and filter data based on the row and column fields that you specify.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
ROW
Returns the row number of a reference.
Lookup & ReferenceLookup & Matching Essentials
ROWS
Returns the number of rows in a reference.
Lookup & ReferenceLookup & Matching Essentials
RTD
Retrieves real-time data from a program that supports COM automation.
Lookup & ReferenceLookup & Matching Essentials
SORT
Sorts the contents of a range or array.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
SORTBY
Sorts the contents of a range or array based on the values in a corresponding range or array.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
TAKE
Returns a specified number of contiguous rows or columns from the start or end of an array.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
TOCOL
Returns the array in a single column.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
TOROW
Returns the array in a single row.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
TRANSPOSE
Returns the transpose of an array.
Lookup & ReferenceLookup & Matching Essentials
TRIMRANGE
Scans in from the edges of a range or array until it finds a non-blank cell (or value), it then excludes those blank rows or columns.
Lookup & ReferenceLookup & Matching EssentialsModern Excel Functions
UNIQUE
Returns a list of unique values in a list or range.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
VLOOKUP
Looks in the first column of an array and moves across the row to return the value of a cell.
Lookup & ReferenceBeginner EssentialsLookup & Matching EssentialsMost Used
VSTACK
Appends arrays vertically and in sequence to return a larger array.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
WRAPCOLS
Wraps the provided row or column of values by columns after a specified number of elements.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
WRAPROWS
Wraps the provided row or column of values by rows after a specified number of elements.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
XLOOKUP
Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.
Lookup & ReferenceBeginner EssentialsLookup & Matching EssentialsModern Excel Functions
XMATCH
Returns the relative position of an item in an array or range of cells.
Lookup & ReferenceLookup & Matching EssentialsModern Excel FunctionsMost Used