List of Excel Lookup and Reference Functions with Syntax and Arguments

This reference table lists all officially documented Lookup and Reference Functions. It is based on the Excel Help documentation and linked in each row when clicking on the function name. Export the table and/or display additional columns via the symbols to the top right of the table. Please report errors or inconsistencies via the comment function at the bottom of this page. Also check the main table with List of all Excel Functions.

#Excel FunctionDescriptionSyntax with ArgumentsSyntax onlyArguments only
1ADDRESSObtain the address of a cell in a worksheet, given specified row and column numbers=ADDRESS(row_numrow_num [required]: A numeric value that specifies the row number to use in the cell reference., column_numcolumn_num [required]: A numeric value that specifies the column number to use in the cell reference., [abs_numabs_num [optional]: A numeric value that specifies the type of reference to return.], [a1a1 [optional]: A logical value that specifies the A1 or R1C1 reference style.], [sheet_textsheet_text [optional]: A text value that specifies the name of the worksheet to be used as the external reference.])=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
  • row_num (required): A numeric value that specifies the row number to use in the cell reference.
  • column_num (required): A numeric value that specifies the column number to use in the cell reference.
  • abs_num (optional): A numeric value that specifies the type of reference to return.
  • a1 (optional): A logical value that specifies the A1 or R1C1 reference style.
  • sheet_text (optional): A text value that specifies the name of the worksheet to be used as the external reference.
2AREASReturns the number of areas in a reference=AREAS(referencereference [required]: A reference to a cell or range of cells and can refer to multiple areas.)=AREAS(reference)
  • reference (required): A reference to a cell or range of cells and can refer to multiple areas.
3CHOOSEUses index_num to return a value from the list of value arguments=CHOOSE(index_numindex_num [required]: Specifies which value argument is selected., value1value1 [required]: Value arguments from which CHOOSE selects a value or an action to perform based on index_num., [value2value2 [optional]: Additional values], ...)=CHOOSE(index_num, value1, [value2], ...)
  • index_num (required): Specifies which value argument is selected.
  • value1 (required): Value arguments from which CHOOSE selects a value or an action to perform based on index_num.
  • value2 (optional): Additional values
4COLUMNReturns the column number of the given cell reference.=COLUMN([referencereference [optional]: The cell or range of cells for which you want to return the column number.])=COLUMN([reference])
  • reference (optional): The cell or range of cells for which you want to return the column number.
5COLUMNSReturns the number of columns in an array or reference=COLUMNS(arrayarray [required]: An array or array formula, or a reference to a range of cells for which you want the number of columns.)=COLUMNS(array)
  • array (required): An array or array formula, or a reference to a range of cells for which you want the number of columns.
6FILTERFilters a range of data based on criteria you define=FILTER (arrayarray [required]: Range to filter, includeinclude [required]: Filter criteria, [if_emptyif_empty [optional]: Return value if no results])=FILTER (array, include, [if_empty])
  • array (required): Range to filter
  • include (required): Filter criteria
  • if_empty (optional): Return value if no results
7FORMULATEXTReturns a formula as a string=FORMULATEXT(referencereference [required]: A reference to a cell or range of cells.)=FORMULATEXT(reference)
  • reference (required): A reference to a cell or range of cells.
8GETPIVOTDATAReturns visible data from a PivotTable=GETPIVOTDATA(data_fielddata_field [required]: The name of the PivotTable field that contains the data that you want to retrieve., pivot_tablepivot_table [required]: A reference to any cell, range of cells, or named range of cells in a PivotTable., [field1, item1, field2, item2field1, item1, field2, item2 [optional]: Additional optional fields, items and references], ...)=GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)
  • data_field (required): The name of the PivotTable field that contains the data that you want to retrieve.
  • pivot_table (required): A reference to any cell, range of cells, or named range of cells in a PivotTable.
  • field1, item1, field2, item2 (optional): Additional optional fields, items and references
9HLOOKUPSearches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array=HLOOKUP(lookup_valuelookup_value [required]: The value to be found in the first row of the table., table_arraytable_array [required]: A table of information in which data is looked up., row_index_numrow_index_num [required]: The row number in table_array from which the matching value will be returned., [range_lookuprange_lookup [optional]: A logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match.])=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
  • lookup_value (required): The value to be found in the first row of the table.
  • table_array (required): A table of information in which data is looked up.
  • row_index_num (required): The row number in table_array from which the matching value will be returned.
  • range_lookup (optional): A logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match.
10HYPERLINKCreates a shortcut that jumps to another location in the current workbook or to an address=HYPERLINK(link_locationlink_location [required]: The path and file name to the document to be opened., [friendly_namefriendly_name [optional]: The jump text or numeric value that is displayed in the cell.])=HYPERLINK(link_location, [friendly_name])
  • link_location (required): The path and file name to the document to be opened.
  • friendly_name (optional): The jump text or numeric value that is displayed in the cell.
11INDEXReturns a value or the reference to a value from within a table or range=INDEX(arrayarray [required]: A range of cells or an array constant., row_numrow_num [required]: Selects the row in array from which to return a value. Optional if column_num is provided. , [column_numcolumn_num [optional]: Selects the column in array from which to return a value. ])=INDEX(array, row_num, [column_num])
  • array (required): A range of cells or an array constant.
  • row_num (required): Selects the row in array from which to return a value. Optional if column_num is provided.
  • column_num (optional): Selects the column in array from which to return a value.
12INDIRECTReturns the reference specified by a text string=INDIRECT(ref_textref_text [required]: A reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string., [a1a1 [optional]: A logical value that specifies what type of reference is contained in the cell ref_text.])=INDIRECT(ref_text, [a1])
  • ref_text (required): A reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string.
  • a1 (optional): A logical value that specifies what type of reference is contained in the cell ref_text.
13LOOKUPLooks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range=LOOKUP(lookup_valuelookup_value [required]: A value that LOOKUP searches for in the first vector., lookup_vectorlookup_vector [required]: A range that contains only one row or one column. , [result_vectorresult_vector [optional]: A range that contains only one row or column. ])=LOOKUP(lookup_value, lookup_vector, [result_vector])
  • lookup_value (required): A value that LOOKUP searches for in the first vector.
  • lookup_vector (required): A range that contains only one row or one column.
  • result_vector (optional): A range that contains only one row or column.
14MATCHSearches for a specified item in a range of cells, and then returns the relative position of that item in the range=MATCH(lookup_valuelookup_value [required]: The value that you want to match in lookup_array., lookup_arraylookup_array [required]: The range of cells being searched., [match_typematch_type [optional]: The number -1, 0, or 1.])=MATCH(lookup_value, lookup_array, [match_type])
  • lookup_value (required): The value that you want to match in lookup_array.
  • lookup_array (required): The range of cells being searched.
  • match_type (optional): The number -1, 0, or 1.
15OFFSETReturns a reference to a range that is a specified number of rows and columns from a cell or range of cells=OFFSET(referencereference [required]: The reference from which you want to base the offset., rowsrows [required]: The number of rows, up or down, that you want the upper-left cell to refer to., colscols [required]: The number of columns, to the left or right, that you want the upper-left cell of the result to refer to., [heightheight [optional]: The height, in number of rows, that you want the returned reference to be.], [widthwidth [optional]: The width, in number of columns, that you want the returned reference to be.])=OFFSET(reference, rows, cols, [height], [width])
  • reference (required): The reference from which you want to base the offset.
  • rows (required): The number of rows, up or down, that you want the upper-left cell to refer to.
  • cols (required): The number of columns, to the left or right, that you want the upper-left cell of the result to refer to.
  • height (optional): The height, in number of rows, that you want the returned reference to be.
  • width (optional): The width, in number of columns, that you want the returned reference to be.
16ROWReturns the row number of a reference=ROW([referencereference [optional]: The cell or range of cells for which you want the row number.])=ROW([reference])
  • reference (optional): The cell or range of cells for which you want the row number.
17ROWSReturns the number of rows in a reference or array=ROWS(arrayarray [required]: An array, an array formula, or a reference to a range of cells for which you want the number of rows.)=ROWS(array)
  • array (required): An array, an array formula, or a reference to a range of cells for which you want the number of rows.
18RTDRetrieves real-time data from a program that supports COM automation=RTD(progidprogid [required]: The name of the ProgID of a registered COM automation add-in that has been installed on the local computer., serverserver [required]: Name of the server where the add-in should be run., topic1topic1 [required]: Parameter that represent a unique piece of real-time data, [topic2topic2 [optional]: Additional parameters], ...)=RTD(progid, server, topic1, [topic2], ...)
  • progid (required): The name of the ProgID of a registered COM automation add-in that has been installed on the local computer.
  • topic1 (required): Parameter that represent a unique piece of real-time data
  • topic2 (optional): Additional parameters
  • server (required): Name of the server where the add-in should be run.
19SORTSorts the contents of a range or array=SORT(arrayarray [required]: The range, or array to sort, [sort_indexsort_index [required]: A number indicating the row or column to sort by], [sort_ordersort_order [optional]: Sort order: 1 for ascending order (default), -1 for descending order], [by_colby_col [optional]: A logical value indicating the desired sort direction; FALSE to sort by row (default), TRUE to sort by colum])=SORT(array, [sort_index], [sort_order], [by_col])
  • array (required): The range, or array to sort
  • sort_index (required): A number indicating the row or column to sort by
  • sort_order (optional): Sort order: 1 for ascending order (default), -1 for descending order
  • by_col (optional): A logical value indicating the desired sort direction; FALSE to sort by row (default), TRUE to sort by colum
20SORTBYSorts the contents of a range or array based on the values in a corresponding range or array=SORTBY(arrayarray [required]: The range, or array to sort, by_array1by_array1 [required]: A number indicating the row or column to sort by, [sort_order1sort_order1 [optional]: Sort order: 1 for ascending order (default), -1 for descending order], [by_array2, sort_order2by_array2, sort_order2 [optional]: Additional ranges/arrays and sort order]=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2]
  • array (required): The range, or array to sort
  • by_array1 (required): A number indicating the row or column to sort by
  • sort_order1 (optional): Sort order: 1 for ascending order (default), -1 for descending order
  • by_array2, sort_order2 (optional): Additional ranges/arrays and sort order
21TRANSPOSEReturns a vertical range of cells as a horizontal range, or vice ersa. =TRANSPOSE(arrayarray [required]: The array or range of cells to transpose. )=TRANSPOSE(array)
  • array (required): The array or range of cells to transpose.
22UNIQUEReturns a list of unique values in a list or range=UNIQUE(arrayarray [required]: The range or array from which to return unique rows or columns,[by_colby_col [optional]: A logical value indicating how to compare. TRUE for columns/FALSE for rows.],[exactly_onceexactly_once [optional]: A logical value indicating rows or colums that occur exactly. TRUE for columns/FALSE for rows.])=UNIQUE(array,[by_col],[exactly_once])
  • array (required): The range or array from which to return unique rows or columns
  • by_col (optional): A logical value indicating how to compare. TRUE for columns/FALSE for rows.
  • exactly_once (optional): A logical value indicating rows or colums that occur exactly. TRUE for columns/FALSE for rows.
23VLOOKUPFind values in a table or a range by row=VLOOKUP (lookup_valuelookup_value [required]: The value to look up which in first column of TABLE_ARRAY argument., table_arraytable_array [required]: The range of cells in which to search, col_index_numcol_index_num [optional]: Column number of value to return from TABLE_ARRAY argument, [range_lookuprange_lookup [optional]: Logical value to search approximate (1/TRUE) or exact (0/FALSE)])=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
  • lookup_value (required): The value to look up which in first column of TABLE_ARRAY argument.
  • table_array (required): The range of cells in which to search
  • col_index_num (optional): Column number of value to return from TABLE_ARRAY argument
  • range_lookup (optional): Logical value to search approximate (1/TRUE) or exact (0/FALSE)
24XLOOKUPFind values in a table or a range by row=XLOOKUP(lookup_valuelookup_value [required]: The value to look up, lookup_arraylookup_array [required]: Array or range to search, return_arrayreturn_array [required]: Array or range to return, [if_not_foundif_not_found [optional]: Value to return if match is not found], [match_modematch_mode [optional]: Match mode 0, 1, -1, or 2], [search_modesearch_mode [optional]: Search mode 0, 1, -1, 2, or -2]) =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
  • lookup_value (required): The value to look up
  • lookup_array (required): Array or range to search
  • return_array (required): Array or range to return
  • if_not_found (optional): Value to return if match is not found
  • match_mode (optional): Match mode 0, 1, -1, or 2
  • search_mode (optional): Search mode 0, 1, -1, 2, or -2
25XMATCHSearches for a specified item in an array or range of cells, and then returns the item's relative position=XMATCH(lookup_valuelookup_value [required]: The value to look up, lookup_arraylookup_array [required]: Array or range to search, [match_modematch_mode [optional]: Match mode 0, 1, -1, or 2], [search_modesearch_mode [optional]: Search mode 0, 1, -1, 2, or -2]) =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
  • lookup_value (required): The value to look up
  • lookup_array (required): Array or range to search
  • match_mode (optional): Match mode 0, 1, -1, or 2
  • search_mode (optional): Search mode 0, 1, -1, 2, or -2

Created by admin on 2/9/2021

 

0 Comments for '(Excel) - List of Excel Lookup and Reference Functions with Syntax and Arguments'   

 
Email me replies   

Please kindly review and accept Terms of Use and Cookie & Policy Policy