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 Function | Description | Syntax with Arguments | Syntax only | Arguments only |
|---|---|---|---|---|---|
| 1 | ADDRESS | Obtain 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]) |
|
| 2 | AREAS | Returns 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) |
|
| 3 | CHOOSE | Uses 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], ...) |
|
| 4 | COLUMN | Returns 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]) |
|
| 5 | COLUMNS | Returns 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) |
|
| 6 | FILTER | Filters 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]) |
|
| 7 | FORMULATEXT | Returns a formula as a string | =FORMULATEXT(referencereference [required]: A reference to a cell or range of cells.) | =FORMULATEXT(reference) |
|
| 8 | GETPIVOTDATA | Returns 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], ...) |
|
| 9 | HLOOKUP | Searches 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]) |
|
| 10 | HYPERLINK | Creates 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]) |
|
| 11 | INDEX | Returns 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]) |
|
| 12 | INDIRECT | Returns 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]) |
|
| 13 | LOOKUP | Looks 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]) |
|
| 14 | MATCH | Searches 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]) |
|
| 15 | OFFSET | Returns 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]) |
|
| 16 | ROW | Returns the row number of a reference | =ROW([referencereference [optional]: The cell or range of cells for which you want the row number.]) | =ROW([reference]) |
|
| 17 | ROWS | Returns 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) |
|
| 18 | RTD | Retrieves 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], ...) |
|
| 19 | SORT | Sorts 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]) |
|
| 20 | SORTBY | Sorts 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] |
|
| 21 | TRANSPOSE | Returns 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) |
|
| 22 | UNIQUE | Returns 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]) |
|
| 23 | VLOOKUP | Find 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]) |
|
| 24 | XLOOKUP | Find 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]) |
|
| 25 | XMATCH | Searches 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]) |
|
Advertising for Consideration:
Ads for Consideration:
Please kindly review and accept Terms of Use and Cookie & Policy Policy
0 Comments for '(Excel) - List of Excel Lookup and Reference Functions with Syntax and Arguments' [post comment in main thread]