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
- How to use the IFS function in Excel 2016the ifs function in excel is a logical function introduced in excel 2016. this function is a function that replaces the nested if function and is much easier to use. the ifs function checks one or more conditions and returns a value that meets the first true condition.
- How to write SEO standard description tag?the site's description tag will provide google and other search engines with a summary of the content of the page or article. the description tag can be one or three simple sentences. google may use the content in the description as a snippet for the page, and display it in the search results if it matches the search user keyword.
- How to use Lookup function in Excellookup in excel is a reference function, the most commonly used search function in excel.
- MINA and MAXA functions in Exceluse the mina () and maxa () functions to find the minimum and maximum values in a list of values that include logical values and numerical representations in text in a reference.
- MS Excel - Lesson 5: Excel formulas and functionsthe formula in excel is a program that performs calculations on data tables. these formulas perform very precise operations such as adding, multiplying, or comparing values in worksheets.
- 8 little-known Excel functions that can save you a lot of workeven seasoned excel users often find themselves stuck performing tasks manually that could be automated with a few clever functions.
- 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.
- Instructions for searching and replacing in Excel tableswith excel spreadsheets containing hundreds and thousands of data, search and replace by formula will help us save a lot of time, increase work efficiency compared to manual methods.
- How to fix the Circular Reference error in Excelthe error of the circular reference in excel occurs when there is a certain formula added to itself.
- LINEST function - The function returns a line description array using the least square method in Excellinest function: the function returns the line description array using the least squares method. syntax: linest (known_ys, [known_xs], [const], [stats])