Syntax and description of search and reference functions in Excel

Working with Excel spreadsheets, but you do not know all about the search and reference group of functions. You follow the article below to know the function of the function in the search and reference functions.

Syntax and description of search and reference functions in Excel Picture 1

The following article summarizes the syntax and description of the search and reference functions in Excel.

ADDRESS (row_num, column_num, abs_num, a1, sheet_text): The function takes the address of a cell in a worksheet, with the given number of rows and columns.

AREAS (reference): The function returns the number of reference regions in a reference, each reference area can be a cell or a series of continuous cells in the worksheet.

CHOOSE (num, value1, value2): The function takes a value in the input argument list.

COLUMN (reference): The function returns the column number of the given cell reference.

COLUMNS (reference): The function returns the total number of columns of an array or reference range.

GETPIVOTDATA (data_field, pivot_table, field1, item1, field2, item2, .): The function returns the data stored in PrivotTable, the function retrieves summary data from PrivotTable, provided that the summary data can be seen. in the report.

HLOOKUP (lookup_value, table_array, row_index_num, range_lookup): The function looks for a value in the top row of a table or array of values, then returns the value in the same column from the row you specified in the table or array.

HYPERLINK (link_location, friendly_name): The function creates a connection, a link to open a document.

INDEX (reference, row_num, column_num, area_num): Finds a value in a table or an array if its position is known, based on the row and column number.

INDIRECT (ref_text, a1): The function returns a reference from the string, the references can be evaluated immediately to display their contents. Use the function when you want to change a reference to a cell in a formula without changing the formula itself.

LOOKUP (lookup_value, lookup_vector, resulf_vector): Search for vectors . The search function on a row or a column, if found, returns the value of the cell of the same position on the specified row (or column). Use the vector form when there is a list of many values ​​to look up or when the values ​​may change over time.

LOOKUP (lookup_value, array): Search for arrays. The search function on the first row (column) of an array of values, if found, returns the value of the cell of the same position on the last row (column) in the array. Use the array search form when there is a list of few values ​​and those values ​​remain unchanged over time.

MATCH (lookup_value, lookup_array, match_type): The function searches for a specified item in a range of cells and returns the relative position of the item in that range.

OFFSET (reference, rows, cols, height, width): The function returns a reference to a specific range calculated by a cell or a range of cells with the specified number of rows or columns.

ROW (reference): The function returns the row number of a reference.

ROWS (reference): The function returns the total number of lines of the referenced region.

TRANSPOSE (array): The function returns a range of vertical cells as a horizontal range or vice versa. The function must be entered as an array formula in a range with the same number of rows and columns corresponding to the number of columns and rows in the source range.

VLOOKUP (lookup_value, table_array, col_index_num, range_lookup): The function used to look up a value in the first left column of a data table. If found, returns the value in the same row as the value found in the column you specified.

So you know the function of each function in the search and reference function group. Depending on the requirements of the problem, you choose the appropriate application function. Good luck!