Lookup & Reference functions
All 23 functions in this category
VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.HLOOKUPHLOOKUP searches the top row of a range for a key and returns a value from a specified row, supporting exact or approximate matches.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.LOOKUPLOOKUP searches a one-dimensional range for a value and returns the corresponding entry from a result range, or the last value if no exact match is found.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.MATCHMATCH returns the relative position of a lookup value within a one-dimensional range, optionally using exact, less-than, or greater-than matching.XMATCHXMATCH returns the relative position of a lookup value within a one-dimensional range, supporting exact, wildcard and next-larger/smaller matches.OFFSETOFFSET returns a reference to a range that is a specified number of rows and columns from a starting cell, optionally sized by height and width.INDIRECTINDIRECT returns the value or reference specified by a text string, letting you build dynamic cell or range references on the fly.CHOOSECHOOSE returns the value or range from a list of arguments that corresponds to the supplied index number, letting you pick one of several options.CHOOSEROWSCHOOSEROWS returns specific rows from a given array based on one or more row numbers, spilling the selected rows into adjacent cells.CHOOSECOLSCHOOSECOLS returns the selected columns from a given array, preserving order and data types, and spills the result into adjacent cells.ROWROW returns the row number of a given cell or range, or the row of the formula itself when no reference is supplied.COLUMNCOLUMN returns the column number of a given reference, or the column of the cell containing the formula when no reference is supplied.ROWSROWS returns the number of rows in a given array or range, counting every row including headers and blank rows within the reference.COLUMNSCOLUMNS returns the number of columns in a given array or range, letting you quickly determine a table's width.ADDRESSADDRESS returns a text string that represents a cell reference based on supplied row, column and optional absolute/relative settings.AREASAREAS returns the count of distinct, non-contiguous ranges (areas) that a reference comprises, useful for evaluating complex range constructions.TRANSPOSETRANSPOSE flips a row-or-column array, turning rows into columns and columns into rows, returning a spilled array of the same size swapped.HYPERLINKHYPERLINK returns a clickable text string that opens a web page, file, or email address when the user selects it in a spreadsheet.FORMULATEXTFORMULATEXT returns the exact formula text from a referenced cell, allowing you to view or audit spreadsheet logic as a plain-text string.GETPIVOTDATAGETPIVOTDATA extracts a single value from a PivotTable by specifying the data field and optional field/item filters, returning the exact cell content.RTDRTD returns live data from a registered COM server, delivering the requested value or array directly into the spreadsheet cell.