Lookup & Reference

Functions for looking up and referencing data in Excel

ADDRESS Function

The Excel ADDRESS function dynamically generates cell references as text strings based on specified row and column numbers. This powerful function enables flexible reference creation for use in other formulas, INDIRECT lookups, and dynamic worksheet navigation.

AREAS Function

The Excel AREAS function counts the number of distinct ranges (areas) within a given reference. An 'area' represents a continuous block of cells or a single cell, making AREAS essential for analyzing complex range structures.

CHOOSE Function

The CHOOSE function dynamically selects and returns one value from a predefined list based on a numeric index. It's like a menu selector that picks exactly which item you want based on your position number, perfect for creating flexible lookups and conditional value returns without nested IF statements.

CHOOSECOLS Function

The CHOOSECOLS function extracts specific columns from an array and returns them in a new dynamic array. Perfect for data cleaning, reshaping datasets, and creating custom column views without complex formulas or helper columns.

CHOOSEROWS Function

The CHOOSEROWS function extracts specific rows from an array, returning them in a new dynamic array. This powerful function enables precise row selection by position, supporting both positive and negative indices for flexible data extraction and manipulation.

COLUMN Function

The COLUMN function returns the column number of a specified cell reference. This is essential for dynamic formulas, table structuring, and position-based calculations in Excel spreadsheets.

COLUMNS Function

The Excel COLUMNS function returns the number of columns in a specified array, range reference, or array formula. It's a simple yet powerful tool for dynamically determining array dimensions in worksheet formulas.

DROP Function

The Excel DROP function removes a specified number of rows or columns from the beginning or end of an array. Perfect for cleaning data by stripping headers, footers, or unwanted edges from dynamic arrays.

EXPAND Function

The EXPAND function transforms arrays by extending them to specified dimensions, automatically filling new areas with customizable padding values. This dynamic array function is essential for reshaping data structures in modern Excel workflows.

FILTER Function

The FILTER function dynamically extracts rows or columns from an array based on specified criteria, returning only the matching records. This powerful dynamic array function revolutionizes data analysis by eliminating the need for complex helper columns or manual filtering.

FORMULATEXT Function

The FORMULATEXT function extracts and returns the formula from a specified cell as a readable text string. Perfect for formula auditing, documentation, and dynamic formula display in reports.

GETPIVOTDATA Function

The GETPIVOTDATA function extracts specific data values from a PivotTable by referencing field names and items. This powerful function enables precise data retrieval from complex PivotTable summaries without manual cell referencing.

GROUPBY Function

The GROUPBY function dynamically groups and aggregates data in Excel, creating pivot table-like summaries directly in formulas. It combines grouping, aggregation, sorting, and filtering capabilities into a single powerful function for advanced data analysis.

HLOOKUP Function

The HLOOKUP function performs horizontal lookups by searching for a specified value in the top row of a table and returning a value from the same column in a designated row below. Perfect for horizontally organized data structures like category headers across the top with related data underneath.

HSTACK Function

The HSTACK function horizontally combines multiple arrays or ranges side by side into a single larger array. Perfect for merging datasets, creating summary tables, or building dynamic reports from separate data sources.

HYPERLINK Function

The HYPERLINK function transforms any cell into a clickable link that opens web pages, documents, or navigates to specific locations within Excel workbooks. Perfect for creating interactive dashboards, reports, and navigation systems.

IMAGE Function

The IMAGE function embeds images directly into Excel cells from web URLs, enabling dynamic visual data representation. Perfect for product catalogs, employee directories, inventory lists, and educational materials where visual context enhances data understanding.

INDEX Function

The INDEX function retrieves a value or reference from a specified position within a range or array. It's a cornerstone of Excel lookup operations, enabling precise data extraction by row and column coordinates.

INDIRECT Function

The INDIRECT function dynamically converts text strings into actual cell references, enabling flexible formula creation that adapts to changing conditions without editing the formula structure itself.

ISLOGICAL Function

The ISLOGICAL function determines whether a given value is a logical (boolean) value, returning TRUE for TRUE/FALSE values and FALSE for all other data types including text representations of logical values.

LOOKUP Function

The LOOKUP function searches for a value in a one-dimensional vector or the first row/column of a multi-dimensional array and returns a corresponding value from the same position in another vector or the last row/column of the array. It performs approximate matching when exact matches aren't found.

MATCH Function

The MATCH function searches for a specified value within a range of cells and returns its relative position. Perfect for dynamic lookups when paired with INDEX, MATCH excels at finding positions rather than values themselves.

OFFSET Function

The OFFSET function dynamically creates a cell reference by shifting from a starting point by a specified number of rows and columns, returning a flexible range reference ideal for dynamic charts, dashboards, and data analysis.

PIVOTBY Function

The PIVOTBY function creates dynamic data summaries through formulas, enabling row and column grouping with flexible aggregation. Perfect for generating PivotTable-style reports directly in cells without using the PivotTable interface.

ROW Function

The ROW function in Excel returns the row number of a specified cell reference or the current cell if no reference is provided. This is essential for dynamic formulas, array operations, and creating position-aware calculations.

ROWS Function

The ROWS function in Excel returns the total number of rows in a specified cell range, array, or array formula. It's an essential tool for dynamic formulas that need to adapt based on data dimensions.

RTD Function

The Excel RTD function enables real-time data retrieval from COM automation add-ins, providing dynamic updates from external data sources like stock quotes, market feeds, or custom applications without manual recalculation.

SORT Function

The SORT function dynamically sorts the contents of a range or array, returning a spilled array in the same shape as the input. Perfect for creating live-updating sorted lists without manual sorting.

SORTBY Function

The SORTBY function dynamically sorts a range or array based on criteria from one or more corresponding arrays. This powerful dynamic array function enables flexible sorting operations beyond simple alphabetical or numerical order.

STOCKHISTORY Function

The STOCKHISTORY function retrieves historical stock market data for financial instruments and returns it as a dynamic array. Perfect for financial analysis, trend tracking, and investment research directly within Excel spreadsheets.

TAKE Function

The Excel TAKE function extracts a specified number of contiguous rows and/or columns from the beginning or end of an array. It's perfect for grabbing the top N rows, first N columns, bottom N rows, or any combination from your data ranges without manual copying or OFFSET formulas.

TOCOL Function

The TOCOL function transforms any array or range into a single column, making it perfect for data reshaping, dynamic lists, and preparing data for other functions that require single-column input.

TOROW Function

The TOROW function transforms any array or range into a single horizontal row, making it perfect for data reshaping and dynamic array operations in modern Excel.

TRANSPOSE Function

The Excel TRANSPOSE function flips the orientation of a range or array, converting vertical data into horizontal layout or horizontal data into vertical. This powerful array function rotates rows into columns and columns into rows without duplicating source data.

TRIMRANGE Function

The TRIMRANGE function intelligently removes empty rows and columns from the outer edges of a range or array, creating a compact dataset by excluding leading and trailing blanks automatically.

UNIQUE Function

The UNIQUE function extracts distinct values from a range or array, automatically removing duplicates and returning a dynamic list. Perfect for cleaning datasets, creating dropdown lists, and preparing data for pivot tables without manual filtering.

VLOOKUP Function

The Excel VLOOKUP function looks for a value in the leftmost column of a table and returns a value in the same row from a column you specify.

VSTACK Function

The VSTACK function vertically stacks multiple arrays into a single larger array by appending them row-wise. This dynamic array function is perfect for combining datasets with matching column structures into one comprehensive table.

WRAPCOLS Function

The WRAPCOLS function transforms a one-dimensional array or vector into a two-dimensional array by wrapping elements into columns with a specified number of rows per column. Perfect for reshaping long lists into organized table formats.

WRAPROWS Function

The WRAPROWS function transforms a linear array or vector into a multi-row 2D array by wrapping elements into rows of specified length. Perfect for reshaping data into matrices or creating organized table structures from single columns or rows.

XLOOKUP Function

The XLOOKUP function performs powerful lookups by searching a specified range or array and returning corresponding values from another range. Unlike traditional lookup functions, XLOOKUP works in any direction, handles multiple return columns, and provides advanced matching and search options for precise results.

XMATCH Function

The XMATCH function returns the relative position of a specified item within a range or array, offering enhanced flexibility over the traditional MATCH function with advanced search and match options.