Skip to content

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

All functions

  • CHOOSEELEMANFull page

    Chooses a value from a list by position.

    =CHOOSE(index_num, value1, [value2], ...)
  • CHOOSECOLSSÜTUNSEÇFull page

    Returns chosen columns of an array.

    =CHOOSECOLS(array, col_num1, [col_num2], ...)
  • COLUMNSÜTUNFull page

    Column number.

    =COLUMN([reference])
  • FILTERFİLTREFull page

    Filters rows that meet a condition. Returns the third argument or #CALC! if nothing matches.

    =FILTER(array, include, [if_empty])
  • HLOOKUPYATAYARAFull page

    Looks up in the first row and returns from the same column.

    =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
  • INDEXİNDİSFull page

    Returns the value at a given row and column of a range.

    =INDEX(array, row_num, [column_num])
  • INDIRECTDOLAYLIFull page

    The value of a reference given as text.

    =INDIRECT(ref_text, [a1])
  • LOOKUPARAFull page

    Finds the last value less than or equal to the lookup value in a sorted list.

    =LOOKUP(lookup_value, lookup_vector, [result_vector])
  • MATCHKAÇINCIFull page

    Position of a value in a list (0 = exact match).

    =MATCH(lookup_value, lookup_array, [match_type])
  • OFFSETKAYDIRFull page

    A reference offset from a starting reference.

    =OFFSET(reference, rows, cols, [height], [width])
  • ROWSATIRFull page

    Row number (the header is row 1).

    =ROW([reference])
  • SORTSIRALAFull page

    Sorts a range (1 ascending, -1 descending).

    =SORT(array, [sort_index], [sort_order], [by_col])
  • SORTBYSIRALAÖLÇÜTFull page

    Sorts a range by the values in another array.

    =SORTBY(array, by_array1, [sort_order1], ...)
  • TAKEALFull page

    Takes rows or columns from the start or end of an array.

    =TAKE(array, rows, [columns])
  • TRANSPOSEDEVRİK_DÖNÜŞÜMFull page

    Transposes an array.

    =TRANSPOSE(array)
  • UNIQUEBENZERSİZFull page

    List of distinct values (count them with COUNTA). TRUE as the third argument keeps values that occur once.

    =UNIQUE(array, [by_col], [exactly_once])
  • VLOOKUPDÜŞEYARAFull page

    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])
  • VSTACKDÜŞEYYIĞFull page

    Stacks arrays vertically.

    =VSTACK(array1, [array2], ...)
  • XLOOKUPÇAPRAZARAFull page

    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])
  • XMATCHÇAPRAZEŞLEŞTİRFull page

    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])

Excel functions →

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