In Excel, the index and match functions work together to get the value of the rows and columns, providing better results and making it easier to replace the Vlookup and Hlookup search functions. If you do not know about this function, please read the following article to clarify.
Searching data in Excel is a fairly regular job for many people, especially computer learners and accountants at the enterprise. Normally, we often use Vlookup to search by column, Hlookup to search by rows to search data in parallel, or in other words, the search data is the same value as the code used to match, compare when searching for results.
How to use the Index and Match functions in Excel?
However, this way we can only perform the comparison of item codes with a corresponding value in the unique lookup table. So if we need to get the value on both the row and the column, the Index function will be your top priority for now because the Index function is a combination of both detection elements on the data table. Especially when combining Index and Match, this combination can sometimes be more effective than Vlookup and Hlookup. So how to use the Index function and the Excel Match function to invite readers to follow the specific example in the following article.
Instructions for using the Index and Match functions in Excel
Download the latest version of Excel: Excel 2013
First, to use the combination of the Index function and the Match function in Excel, you need to understand the syntax and usage of these two functions:
– Index function
+ Array Index function:
Syntax: (Array, Row_num,[Column_num])
– Array: A range of cells or an array constant, required
– Row_num: Select the row in the array from which to return a value
– Column_num: Select the column in the array from which to return a value.
At least one of the Row_num and Column_num arguments is required.
+ Index function as reference:
Syntax: INDEX (Reference, Row_num,[Column_num],[Area_num])
– Reference: Reference region, required
– Row_num: The row index from which to return a reference, required.
– Column_num: The column number from which to return an optional, reference.
– Area_num: Number of range cells will return the value in reference. If Area_num is omitted, the INDEX function uses region 1, optional.
– Match function
Syntax: MATCH (Lookup_value, Lookup_array,[Match_type])
– Lookup_value: Search value (numerical value, text, logical value, cell reference to a number, text or logical value)
– Lookup_array: Array to search, required
– Match_type: Search type. Optional.
There are 3 types of search:
1: Less than (Less than the search value)
0: Exact match (Exact match value)
-1: Greater than (greater than the search value)
When omitting nothing, the default MATCH function is 1.
For example: Exercise on Index and Match functions
For the data tables as shown below, it is required to use the Index and Match functions to fill in the unit price column for each item based on the 2 data tables below.
– Enter the formula in the cell E6 = INDEX ($ B $ 15: $ F $ 19, MATCH (A6, $ B $ 15: $ B $ 19,0), MATCH (B6, $ B $ 15: $ F $ 15,0)), We have the results as shown below
So, above we showed you how to use the combination of the Index function and the Match function on Excel spreadsheets to get values in rows and columns in different tables and fill the values correctly in the data sheet. You can use this method on versions of Office 2013, Office 2010, Office 2007 and Office 2003
In addition, you can refer GAMMADIST function, The function returns the gamma distribution as well as many other mathematical functions available on Taimienphi.vn. GAMMADIST is a function used in the study of variables that may have a skewed distribution. In particular, it is applied in the queue analysis process, an analysis process with high applicability in current practical problems.
Along with that, readers can refer to the FTEST function, this is a type of compatibility function that calculates the F-Test in statistical probability, allowing the calculation of the difference in variance.
To facilitate the input of text you can use the Proper function – function capitalized first letter. The syntax, usage of the first capitalization function have been shared on Taimienphi.vn.