Lookup and Reference Functions in Excel
Lookup and Reference Functions in Excel
Excel boasts a vast library of functions, but don’t be overwhelmed! We’ll explore the most essential functions grouped into intuitive categories. Here is the list of Excel Formulas and Functions. We have included Excel functions, Description, Syntax. Explained with Example Formulas.
Lookup and Reference Functions
Find What You Need Across Your Spreadsheets: Lost in a sea of data across different worksheets? Lookup & Reference Functions come to the rescue! These champions help you retrieve specific data from any location within your workbook. VLOOKUP and HLOOKUP are masters at searching for values and returning corresponding information, while INDEX and MATCH offer ultimate flexibility for customized lookups.
- Retrieve data from different parts of your workbook.
- Search for specific values and return corresponding information (VLOOKUP, HLOOKUP).
- Use a combination of INDEX and MATCH for flexible lookups.
- Reference cells across worksheets for consolidated reports.
Function | Description | Syntax and Formula |
ADDRESS | The ADDRESS function returns a cell address as text, based on a given row and column number. | Syntax: ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) Formula: ADDRESS(2,3,4,TRUE,”Sheet1″)This example will return the address of cell C4 (row 2, column 3) on Sheet1 as $C$4. |
AREAS | The AREAS function in Microsoft Excel returns the number of areas in a reference. | Syntax: AREAS(reference) Formula: AREAS(A1:B2)This formula will return the value 2, since the range A1:B2 contains two areas. |
CHOOSE | The CHOOSE function in Excel is used to select one of a number of values based on an index number. | Syntax: CHOOSE(index_num, value1, [value2], …) Formula: CHOOSE(2, “red”, “blue”, “green”)This example would return “blue” as the output. |
CHOOSECOLS | The CHOOSECOLS function allows you to select columns from a range of cells. It returns an array of the selected column numbers. | Syntax: CHOOSECOLS(range, [criteria]) Formula: CHOOSECOLS(A1:F10, “>50”)This example will return an array of column numbers from A1:F10 that contain values greater than 50. |
CHOOSEROWS | The CHOOSEROWS function returns an array of rows from a list based on a criteria. | Syntax: CHOOSEROWS(list, criteria) Formula: CHOOSEROWS(A1:A10, “>5”)This will return an array of rows from A1:A10 where the value is greater than 5. |
COLUMN | The COLUMN function is an Excel function that returns the column number of a given cell reference. | Syntax: COLUMN(reference) Formula: COLUMN(A1)This will return the value 1, since A1 is the first column in the worksheet. |
COLUMNS | COLUMNS is a function in Microsoft Excel that returns the number of columns in a given array or reference. | Syntax: COLUMNS(array) Formula: COLUMNS(A1:B2)This example would return 2, since the range A1:B2 contains two columns. |
DROPDOWN | The DROPDOWN function creates a dropdown list in a cell. | Syntax: DROPDOWN(list, [selected], [input_title], [input_message]) Formula: DROPDOWN(A1:A5, 2, “Select an Option”, “Choose an Option”) |
EXPAND | The EXPAND function is used to expand a range of cells from a single cell. | Syntax: EXPAND(reference, row_num, column_num) Formula: EXPAND(A1,2,2)This would expand the range of cells from A1 to C2. |
FILTER | The FILTER function is used to filter a range of data based on supplied criteria. It returns a subset of data that meets the criteria. | Syntax: FILTER(array, include, [if_empty]) Formula: FILTER(A2:C10, B2:B10=”Yes”, “No matches”)This example will return the values in column A that correspond to the rows in column B that contain the value “Yes”. If no matches are found, it will return “No matches”. |
FORMULATEXT | The FORMULATEXT function is used to return the formula in a cell as text. | Syntax: FORMULATEXT(reference) Formula: FORMULATEXT(A1)This example would return the formula in cell A1 as text. |
GETPIVOTDATA | The GETPIVOTDATA function in Excel is used to retrieve data stored in a PivotTable. It is used to reference data stored in a PivotTable by using a standard cell reference. | Syntax: GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], …) Formula: GETPIVOTDATA(“Sales”,A2,”Region”,”West”) |
HLOOKUP | HLOOKUP is an Excel function used to look up and retrieve data from a row in a table. It searches for a value in the top row of the table and returns the corresponding value in the same column from another row. | Syntax: HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) Formula: HLOOKUP(B2,A2:D7,3,FALSE)In this example, the HLOOKUP function searches for the value in cell B2 in the first row of the table (A2:D7). It then returns the corresponding value in the third row of the same column. The range_lookup argument is set to FALSE to ensure an exact match is found. |
HSTACK | HSTACK is an Excel function that allows users to combine two or more arrays into one single array. | Syntax: HSTACK(array1, array2, array3, …) Formula: HSTACK({1,2,3}, {4,5,6}, {7,8,9}) returns {1,2,3,4,5,6,7,8,9} |
HYPERLINK | The HYPERLINK function creates a shortcut that jumps to a specific location in the current workbook, opens a document stored on a network server, or opens a web page on the Internet. | Syntax: HYPERLINK(link_location, [friendly_name]) Formula: HYPERLINK(“www.google.com”, “Google”)This will create a link to Google’s website with the friendly name “Google”. |
INDEX | The INDEX function returns a value or reference of the cell at the intersection of a particular row and column within a range. | Syntax: INDEX(array, row_num, [column_num]) Formula: INDEX(A1:C3, 2, 3)This formula will return the value at the intersection of the second row and third column of the range A1:C3. |
INDIRECT | The INDIRECT function returns a reference to a range. It is useful when you want to convert a text string into a valid reference. | Syntax: INDIRECT(ref_text, [a1]) Formula: INDIRECT(“A1”) returns the value of cell A1. |
LOOKUP | The LOOKUP function is used to look up a value in a row or column of data. It can be used to look up a value either vertically (in a column) or horizontally (in a row). | Syntax: LOOKUP(lookup_value, lookup_vector, [result_vector]) Formula: LOOKUP(A2,B2:B6,C2:C6) |
MATCH | MATCH is a function in Excel that looks for a specified item in a range of cells and returns the relative position of that item in the range. | Syntax: MATCH(lookup_value, lookup_array, [match_type])lookup_value: The value to be found in the lookup_array.lookup_array: The range of cells being searched.match_type: [optional] The number -1, 0, or 1. The match_type specifies how Excel matches lookup_value with values in lookup_array. Formula: MATCH(A1,B1:B10,0)This example looks for the value in cell A1 in the range of cells B1:B10. The match_type is set to 0, which means that Excel will find an exact match for the value in A1. The function will return the relative position of the value in A1 in the range B1:B10. |
OFFSET | The OFFSET function returns a reference to a range that is a specified number of rows and columns from a given reference cell. | Syntax: OFFSET(reference, rows, cols, [height], [width]) Formula: OFFSET(B2, 2, 3, 2, 1)This will return the range C4:C5, which is two rows and three columns away from cell B2. |
ROW | The ROW function returns the row number of a cell reference. | Syntax: ROW([reference]) Formula: ROW(A1) returns 1. |
ROWS | The ROWS function returns the number of rows in a given array or range. | Syntax: ROWS(array) Formula: ROWS(A1:B10)This will return the number 10, as there are 10 rows in the range A1:B10. |
RTD | The RTD function in Excel is used to retrieve real-time data from a program that supports COM automation. | Syntax: RTD(progID, server, topic1, [topic2], …) Formula: RTD(“MSFTQuote.RTD”,,”MSFT”) |
SORT | The SORT function in Excel allows users to sort a range of data by one or more columns or rows in either ascending or descending order. | Syntax: SORT(array, [sort_index], [sort_order], [by_col]) Formula: SORT(A2:D6, 2, 1, TRUE)This example will sort the range of data in A2:D6 by the second column in ascending order. |
SORTBY | The SORTBY function sorts a range of data based on one or more columns. | Syntax: SORTBY(array, sort_column1, [sort_order1], [sort_column2], [sort_order2], …) Formula: SORTBY(A2:F6, 2, 1, 5, -1)This example will sort the range A2:F6 based on the values in column 2 in ascending order and the values in column 5 in descending order. |
TAKE | TAKE is an Excel function that returns a specified number of characters from the start of a text string. | Syntax: TAKE(text,num_chars) Formula: TAKE(“Excel”,3)Result: Exc |
TOCOL | The TOCOL function returns the column number of a given cell reference. | Syntax: TOCOL(cell_reference) Formula: TOCOL(“A1”) returns 1 |
TOROW | The TOROW function returns the row number of a reference. | Syntax: TOROW(reference) Formula: TOROW(A1)This example returns the row number of cell A1, which is 1. |
TRANSPOSE | The TRANSPOSE function in Excel is used to transpose a range of cells from vertical to horizontal or vice versa. | Syntax: TRANSPOSE(array) Formula: TRANSPOSE(A1:C3)This example would take the range of cells from A1 to C3 and transpose them so that the rows become columns and the columns become rows. |
UNIQUE | The UNIQUE function in Excel is used to return a list of unique values from a list or range. | Syntax: UNIQUE(array, [by_col], [exactly_once])Array: The array or range of cells from which to extract unique values.By_col: (optional) A logical value that specifies whether to return unique rows or columns. If set to TRUE, the function will return unique rows; if set to FALSE, the function will return unique columns. The default value is FALSE.Exactly_once: (optional) A logical value that specifies whether to return only values that appear exactly once in the list or range. The default value is FALSE. Formula: UNIQUE(A1:A10)This will return a list of unique values from the range A1:A10. |
VLOOKUP | VLOOKUP is an Excel function used to look up and retrieve data from a specific column in a table. It searches for a value in the leftmost column of a table and returns a value in the same row from another column. | Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Formula: VLOOKUP(A2, C2:E5, 3, FALSE)This example looks up the value in cell A2 in the leftmost column of the table in cells C2 to E5 and returns the value in the same row from the third column of the table. |
VSTACK | The VSTACK function in Excel is used to stack two or more vertical ranges of data on top of each other. | Syntax: VSTACK(array1, array2, [array3], …) Formula: VSTACK(A1:A5, B1:B5) |
WRAPCOLS | The WRAPCOLS function in Microsoft Excel is used to wrap the contents of a cell across multiple columns. | Syntax: WRAPCOLS(cell_reference, number_of_columns) Formula: WRAPCOLS(A1, 3)This will wrap the contents of cell A1 across 3 columns. |
WRAPROWS | WRAPROWS is a Microsoft Excel function that allows you to wrap text in a cell to multiple lines. This is useful when you have a long text string that needs to be displayed in a cell. | Syntax: WRAPROWS(cell_reference) Formula: WRAPROWS(A1) |
XLOOKUP | XLOOKUP is an Excel function that looks for a value in the first column of a table array and returns the corresponding value from another column in the same row. | Syntax: XLOOKUP(lookup_value, lookup_array, return_column_number, [not_found], [match_mode], [search_mode]) Formula: XLOOKUP(A2, B2:E5, 3, “Not Found”, 0, 1)In this example, the lookup_value is A2, the lookup_array is B2:E5, the return_column_number is 3, the not_found value is “Not Found”, the match_mode is 0 and the search_mode is 1. |
XMATCH | XMATCH: This function returns the position of a value in a given array that matches a specified value in a specified order. | Syntax: XMATCH(lookup_value, lookup_array, [match_type]) Formula: XMATCH(“Apple”, {“Orange”, “Banana”, “Apple”, “Grape”}, 0)This example will return 3, since “Apple” is the third item in the array. |
You May Like to Browers More


