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.
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!
You should read it
- SHEETS function - The function returns the number of pages in a reference in Excel
- SEARCH () and SEARCHB () functions in Excel
- How to use the Search function in Excel
- Logical functions (logical) in Excel
- Matrix functions in Excel
- How to fix the SUM function doesn't add up in Excel
- Complete financial functions in Excel you should know
- How to use the IFS function in Excel 2016
May be interested
- The syntax and function of each function in the group of statistical functions in Excelthe statistical function is divided into 3 subgroups: the statistical group, the probability distribution function group and the linear regression and correlation function group.
- Summary of information functions in Excelto facilitate the selection of functions to suit the requirements when you need to process information in excel spreadsheets. the following article summarizes the functions along with the functions of each function group in the excel spreadsheet.
- Logical functions (logical) in Excellogical functions are used a lot during data processing in excel. the article summarizes the syntax and functions of functions in logical function groups in excel.
- Spacing, spacing words in Wordspacing, spacing words in word. to quickly line up in word, select (highlight) the paragraph or the whole text.
- How to use the RANK function in Excelthe rank function in excel is a function used by many people in data processing. for accountants, the rank function is very popular.
- Change the width of columns and the height of rows in Excelthe default column widths and row heights in excel may not match the data you enter. you want to change the width, height of rows and columns so that data is displayed fully on cells in excel.