XLOOKUP, INDEX/MATCH and friends — find values, pull data across sheets and build dynamic ranges. This page lists all 28 of them; each links to a full lesson with an example you can copy and a practice file.
ADDRESSADDRESS returns a cell reference as text from row and column numbers — often combined with MATCH to…AREASAREAS counts separate ranges in a reference — used with INDEX’s area_num argument to switch between tables.CHOOSECHOOSE returns the n-th item from a list of values you type in the formula.COLUMNCOLUMN returns the column number (A = 1). Handy for VLOOKUP formulas that fill across.COLUMNSCOLUMNS counts columns in a range — useful for dynamic column numbers and checks.CUBEMEMBERCUBEMEMBER returns a member (like a year or product) from the Data Model, which CUBEVALUE can then reference.CUBESETCUBESET defines a sorted set from the Data Model — combine it with CUBERANKEDMEMBER for top-10 lists that…CUBEVALUECUBEVALUE pulls a measure from the Power Pivot Data Model into any cell, so you can build free-form…FORMULATEXTFORMULATEXT displays the formula of another cell — perfect for documenting models and training sheets.GETPIVOTDATAGETPIVOTDATA pulls a value from a pivot by field names, not by cell position — so your report…HLOOKUPHLOOKUP is VLOOKUP turned sideways: it searches the top row and returns a value from a row below.HYPERLINKHYPERLINK builds a clickable link — to websites, files, or other sheets in the same workbook.IMAGEIMAGE shows an image from a web address inside a cell — product catalogues that sort and filter…IMPORTCSVIMPORTCSV (2026) is the simple version of IMPORTTEXT for standard CSV files: one argument, a refreshable dynamic array.IMPORTTEXTIMPORTTEXT (2026) loads a .txt, .csv or .tsv file into a refreshable dynamic array with one formula, with…INDEXINDEX returns the value at a given row (and optional column) of a range. Combined with MATCH it…INDIRECTINDIRECT turns text such as "Mar!B5" into a real reference — the key to dependent dropdowns and summary…LOOKUPLOOKUP is an older function that needs sorted data for normal use — but its array form powers…MATCHMATCH returns the position of a value in a list — the “where is it?” half of INDEX/MATCH.OFFSETOFFSET returns a range a set number of rows and columns away, optionally with a new size. Classic…ROWROW returns the row number of a reference (or of the cell it is in). It is the…ROWSROWS counts the rows in a range or array — the classic helper for top-N lists that fill…STOCKHISTORYSTOCKHISTORY fetches daily, weekly or monthly prices for a stock or currency pair into a spill range.TRANSPOSETRANSPOSE turns a vertical range horizontal and vice versa, and stays linked to the source.VLOOKUPVLOOKUP finds a value in the first column of a table and returns a value from another column…WEBSERVICEWEBSERVICE downloads text from a web address (Windows desktop only). Combined with FILTERXML or TEXTAFTER it can read…XLOOKUPXLOOKUP finds a value in one range and returns the matching item from another. It looks in any…XMATCHXMATCH returns the position of an item, defaulting to exact match, and can search from the end or…