This reference lists 31 Excel lookup & reference functions, ordered by frequency of use, with high-frequency functions marked ⭐. Each function includes its syntax and an example.

Function Description Example
CHOOSE ⭐ Chooses a value from a list of values =CHOOSE(3,"Apple","Banana","Orange") — Returns the 3rd item "Orange" from the list
COLUMN ⭐ Returns the column number of a reference =COLUMN(A1) — Returns the column number of A1, which is 1
FILTER ⭐ Filters a range of data based on criteria you define =FILTER(A1:C10,B1:B10=">60") — Filters rows of A:C where column B is greater than 60
HLOOKUP ⭐ Looks in the top row of an array and returns the value of the indicated cell =HLOOKUP("Name",A1:E5,3,FALSE) — Looks up "Name" in row 1 and returns the value from row 3
INDEX ⭐ Uses an index to choose a value from a reference or array =INDEX(A1:C10,3,2) — Returns the value at row 3, column 2 of A1:C10
MATCH ⭐ Looks up values in a reference or array =MATCH("Apple",A1:A10,0) — Finds the position of "Apple" in column A
ROW ⭐ Returns the row number of a reference =ROW(A1) — Returns the row number of A1, which is 1
SORT ⭐ Sorts the contents of a range or array =SORT(A1:C10,3,-1) — Sorts A1:C10 by column 3 in descending order
TRANSPOSE ⭐ Returns the transpose of an array =TRANSPOSE(A1:C3) — Transposes a 3x3 range, swapping rows and columns
UNIQUE ⭐ Returns a list of unique values in a list or range =UNIQUE(A1:A10) — Returns the list of unique values in A1:A10
VLOOKUP ⭐ Looks in the first column of an array and moves across the row to return the value of a cell =VLOOKUP("Apple",A:C,3,FALSE) — Looks up "Apple" in column A and returns the value from column C
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. =XLOOKUP("Apple",A1:A10,B1:B10,"Not found",0,-1) — Looks up "Apple" in column A and returns column B, or "Not found" if missing
ADDRESS Returns a reference as text to a single cell in a worksheet =ADDRESS(1,1) — Returns the address of A1 as "$A$1"
AREAS Returns the number of areas in a reference =AREAS(A1:B10) — Returns the number of areas in the reference, which is 1
CHOOSECOLS Returns the specified columns from an array =CHOOSECOLS(A1:D10,1,3) — Returns columns 1 and 3 of A1:D10
CHOOSEROWS Returns the specified rows from an array =CHOOSEROWS(A1:D10,1,3) — Returns rows 1 and 3 of A1:D10
COLUMNS Returns the number of columns in a reference =COLUMNS(A1:C10) — Returns the number of columns in A1:C10, which is 3
DROP Excludes a specified number of rows or columns from the start or end of an array =DROP(A1:C10,2) — Drops the first 2 rows from A1:C10
GETPIVOTDATA Returns data stored in a PivotTable report =GETPIVOTDATA("Sales",$A$3,"Product","Apple") — Extracts the sales of Apple from a PivotTable
HSTACK Appends arrays horizontally and in sequence to return a larger array =HSTACK(A1:A5,B1:B5) — Stacks two arrays horizontally
INDIRECT Returns a reference indicated by a text value =INDIRECT("A"&ROW()) — Dynamically references a cell in column A by row number
LOOKUP Looks up values in a vector or array =LOOKUP(100,A1:A10,B1:B10) — Looks up 100 in column A and returns the value from column B
OFFSET Returns a reference offset from a given reference =OFFSET(A1,2,3) — Returns the cell 2 rows below and 3 columns right of A1
ROWS Returns the number of rows in a reference =ROWS(A1:C10) — Returns the number of rows in A1:C10, which is 10
SINGLE Returns a single value using implicit intersection =SINGLE(A1) — Returns a single value (a simplified INDEX)
SORTBY Sorts the contents of a range or array based on the values in a corresponding range or array =SORTBY(A1:C10,B1:B10,1,C1:C10,-1) — Sorts ascending by column B, then descending by column C
TAKE Returns a specified number of contiguous rows or columns from the start or end of an array =TAKE(A1:C10,5) — Returns the first 5 rows of A1:C10
TOCOL Returns the array in a single column =TOCOL(A1:C3) — Converts a 3x3 range into a single column
TOROW Returns the array in a single row =TOROW(A1:C3) — Converts a 3x3 range into a single row
VSTACK Appends arrays vertically and in sequence to return a larger array =VSTACK(A1:A5,B1:B5) — Stacks two arrays vertically
XMATCH Returns the relative position of an item in an array or range of cells =XMATCH("Apple",A1:A10,0,-1) — Finds the position of "Apple" in column A

From Online Toolbox (www.vba.net)