Excel functions · 37 functions
Excel lookup and reference functions
Functions that find and return values from a table, report the position or size of a range, and filter, sort or reshape tables as dynamic arrays: VLOOKUP, XLOOKUP, INDEX, MATCH, FILTER, UNIQUE and more.
Functions explained in full
- CHOOSEChooses a value from a list by position.
- CHOOSECOLSReturns chosen columns of an array.
- COLUMNColumn number.
- FILTERFilters rows that meet a condition. Returns the third argument or #CALC! if nothing matches.
- HLOOKUPLooks up in the first row and returns from the same column.
- HYPERLINKCreates a link; shows the friendly name.
- INDEXReturns the value at a given row and column of a range.
- INDIRECTThe value of a reference given as text.
- LOOKUPFinds the last value less than or equal to the lookup value in a sorted list.
- MATCHPosition of a value in a list (0 = exact match).
- OFFSETA reference offset from a starting reference.
- ROWRow number (the header is row 1).
- SORTSorts a range (1 ascending, -1 descending).
- SORTBYSorts a range by the values in another array.
- TAKETakes rows or columns from the start or end of an array.
- TRANSPOSETransposes an array.
- UNIQUEList of distinct values (count them with COUNTA). TRUE as the third argument keeps values that occur once.
- VLOOKUPLooks up a value in the first column and returns a value from the same row. Use 0 as the last argument for exact match.
- VSTACKStacks arrays vertically.
- XLOOKUPSearches an array and returns the matching item; the fourth argument if not found.
- XMATCHPosition of a value; exact match by default.
All functions
Chooses a value from a list by position.
=CHOOSE(index_num, value1, [value2], ...)Returns chosen columns of an array.
=CHOOSECOLS(array, col_num1, [col_num2], ...)Column number.
=COLUMN([reference])Filters rows that meet a condition. Returns the third argument or #CALC! if nothing matches.
=FILTER(array, include, [if_empty])Looks up in the first row and returns from the same column.
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])Creates a link; shows the friendly name.
=HYPERLINK(link_location, [friendly_name])Returns the value at a given row and column of a range.
=INDEX(array, row_num, [column_num])The value of a reference given as text.
=INDIRECT(ref_text, [a1])Finds the last value less than or equal to the lookup value in a sorted list.
=LOOKUP(lookup_value, lookup_vector, [result_vector])Position of a value in a list (0 = exact match).
=MATCH(lookup_value, lookup_array, [match_type])A reference offset from a starting reference.
=OFFSET(reference, rows, cols, [height], [width])Row number (the header is row 1).
=ROW([reference])Sorts a range (1 ascending, -1 descending).
=SORT(array, [sort_index], [sort_order], [by_col])Sorts a range by the values in another array.
=SORTBY(array, by_array1, [sort_order1], ...)Takes rows or columns from the start or end of an array.
=TAKE(array, rows, [columns])Transposes an array.
=TRANSPOSE(array)List of distinct values (count them with COUNTA). TRUE as the third argument keeps values that occur once.
=UNIQUE(array, [by_col], [exactly_once])Looks up a value in the first column and returns a value from the same row. Use 0 as the last argument for exact match.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Stacks arrays vertically.
=VSTACK(array1, [array2], ...)Searches an array and returns the matching item; the fourth argument if not found.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Position of a value; exact match by default.
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])- ADDRESSADRES
Cell address as text.
=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) - AREASALANSAY
Number of areas in a reference.
=AREAS(reference) - CHOOSEROWSSATIRSEÇ
Returns chosen rows of an array.
=CHOOSEROWS(array, row_num1, [row_num2], ...) - COLUMNSSÜTUNSAY
Number of columns in a range.
=COLUMNS(array) - DROPBIRAK
Drops rows or columns from the start or end of an array.
=DROP(array, rows, [columns]) - EXPANDGENİŞLET
Expands an array to given dimensions.
=EXPAND(array, rows, [columns], [pad_with]) - FORMULATEXTFORMÜLMETNİ
The formula in a referenced cell as text.
=FORMULATEXT(reference) - GROUPBYGRUPLAÖLÇÜT
Groups rows by fields and aggregates values with a function (a formula pivot).
=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship]) - HSTACKYATAYYIĞ
Stacks arrays horizontally.
=HSTACK(array1, [array2], ...) - PIVOTBYÖZETÖLÇÜT
Cross-tabulates values by row and column fields (a formula pivot).
=PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array], [relative_to]) - ROWSSATIRSAY
Number of rows in a range.
=ROWS(array) - TOCOLSÜTUNA
Returns an array as one column.
=TOCOL(array, [ignore], [scan_by_column]) - TOROWSATIRA
Returns an array as one row.
=TOROW(array, [ignore], [scan_by_column]) - TRIMRANGE
Trims empty rows and columns from the edges of a range.
=TRIMRANGE(range, [trim_rows], [trim_cols]) - WRAPCOLSSÜTUNSAR
Wraps a vector into columns.
=WRAPCOLS(vector, wrap_count, [pad_with]) - WRAPROWSSATIRSAR
Wraps a vector into rows.
=WRAPROWS(vector, wrap_count, [pad_with])
Stop memorising formulas. Describe them.
Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.
Try it free