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)