Excel Functions & Formulas Reference
Complete directory of essential Excel functions. Look up syntax parameters, practical real-world formula examples, and linked step-by-step tutorial guides.
🔎Lookup & Reference Functions
27 functionsTRANSPOSE
Flips the orientation of a range or array from horizontal to vertical, or vice versa.
=TRANSPOSE(array)=TRANSPOSE(A1:D1)TAKE
★ PopularReturns a specified number of contiguous rows or columns from the start or end of an array.
=TAKE(array, rows, [columns])=TAKE(SORT(A2:C50, 3, -1), 5)DROP
★ PopularExcludes a specified number of rows or columns from the start or end of an array.
=DROP(array, rows, [columns])=DROP(A1:C50, 1)CHOOSECOLS
★ PopularReturns specified columns from an array or range.
=CHOOSECOLS(array, col_num1, [col_num2], ...)=CHOOSECOLS(A2:F100, 1, 3, 5)CHOOSEROWS
Returns specified rows from an array or range.
=CHOOSEROWS(array, row_num1, [row_num2], ...)=CHOOSEROWS(A2:F100, 1, 5, 10)LOOKUP
Looks up a value in a single-row or single-column range and returns a value from the same position in another range.
=LOOKUP(lookup_value, lookup_vector, [result_vector])=LOOKUP(A2, D2:D10, E2:E10)CHOOSE
Returns a value from a list of arguments based on an index number.
=CHOOSE(index_num, value1, [value2], ...)=ROW(A5)COLUMN
Returns the column number of a cell reference.
=COLUMN([reference])=COLUMN(C1)SORTBY
Sorts the contents of a range or array based on the values in a corresponding range or array.
=SORTBY(array, by_array1, [sort_order1], ...)=SORTBY(A2:B10, B2:B10, -1)LAMBDA
★ PopularCreates custom, reusable functions in Excel without writing VBA code.
=LAMBDA([parameter1, ...], calculation)=LAMBDA(x, y, x*1.1 + y)TOCOL
★ PopularTransforms a 2D array or range into a single vertical column.
=TOCOL(array, [ignore], [scan_by_column])=TOCOL(A1:C10, 1)TOROW
Transforms a 2D array or range into a single horizontal row.
=TOROW(array, [ignore], [scan_by_column])=TOROW(A1:C10, 1)WRAPROWS
Wraps a 1D vector into a 2D array by rows after reaching a specified number of elements.
=WRAPROWS(vector, wrap_count, [pad_with])=WRAPROWS(A1:A12, 3)WRAPCOLS
Wraps a 1D vector into a 2D array by columns after reaching a specified number of elements.
=WRAPCOLS(vector, wrap_count, [pad_with])=WRAPCOLS(A1:A12, 4)ADDRESS
Creates a cell reference as text from given row and column numbers.
=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])=ROWS(A1:C10)COLUMNS
Returns the total number of columns in a reference or array.
=COLUMNS(array)=COLUMNS(A1:C10)CELL
Returns information about the formatting, location, or contents of a specified cell.
=CELL(info_type, [reference])=MAP(A1:A10, LAMBDA(x, x * 1.1))SORT
Sorts the contents of a range or array automatically in ascending or descending order.
=SORT(array, [sort_index], [sort_order], [by_col])=SORT(A2:B20, 2, -1)REDUCE
Reduces an array to an accumulated value by applying a LAMBDA to each element.
=REDUCE([initial_value], array, lambda)=REDUCE(0, A1:A10, LAMBDA(acc, val, acc + val))SCAN
Scans an array by applying a LAMBDA to accumulate intermediate calculation values.
=SCAN([initial_value], array, lambda)=SCAN(0, A1:A10, LAMBDA(acc, val, acc + val))MAKEARRAY
Returns a calculated array of specified row and column size by applying a LAMBDA.
=MAKEARRAY(rows, cols, lambda)=MAKEARRAY(5, 5, LAMBDA(r, c, r * c))BYROW
Applies a LAMBDA to each row in an array and returns an array of results.
=BYROW(array, lambda)=BYROW(A1:C10, LAMBDA(r, SUM(r)))BYCOL
Applies a LAMBDA to each column in an array and returns an array of results.
=BYCOL(array, lambda)=BYCOL(A1:C10, LAMBDA(c, AVERAGE(c)))ISOMITTED
Checks whether an optional argument in a custom LAMBDA function is missing.
=ISOMITTED(argument)=MAP(A1:A10, LAMBDA(x, x * 1.1))XLOOKUP
★ PopularSearches a range or array for a match and returns an item from a corresponding second range. Replaces VLOOKUP, HLOOKUP, and INDEX/MATCH.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])=VLOOKUP(101, A2:D50, 3, FALSE)INDEX
★ PopularReturns a value or reference to a value from within a table or range at a given row and column position.
=INDEX(array, row_num, [column_num])=INDEX(C2:C100, 5)MATCH
★ PopularSearches for a specified item in a range of cells and returns the relative position of that item.
=MATCH(lookup_value, lookup_array, [match_type])=VLOOKUP(101, A2:D50, 3, FALSE)