ADDRESS
Returns a reference as text to a single cell in a worksheet.
Lookup & ReferenceLookup & Matching Essentials
Lookup & Matching Essentials
Lookup & Matching functions are how you connect tables in Excel. Use them to find values, match rows, return related fields, and build reliable data models.
This collection groups Excel functions that are commonly used for lookup & matching essentials workflows. Instead of browsing hundreds of formulas, you can focus on the exact tools that solve this type of task.
Each function page includes a short explanation and practical usage context. Use these functions as standalone tools or combine them to build more advanced, maintainable formulas.
If you frequently work with reports, dashboards, or recurring templates, bookmarking the key functions from this collection will speed up your day-to-day work.
Tips
- Start with the most common functions in this collection and expand as your problems get more complex.
- Combine these functions with structured tables to keep formulas readable and resilient.
Excel functions in this collection (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