How Match and Index Work?
The most powerful and flexible way to look up data in Excel is the INDEX-MATCH method. It relies on two Excel functions:
The INDEX function
The INDEX function allows you to return the entry in any row and column within an array of numbers. The Excel Index function returns a reference to a cell that lies in a specified row and column of a user-supplied a range of cells.
The most commonly used syntax for the INDEX function is:
=INDEX(reference, row _ num, column_num)
- Array: This is a range of cells or an array constant. If the array contains only one row or column, the corresponding row number or column number argument is optional.
- Row _ num: This selects the row in the array from which to return a value. If row_num is omitted, column_num is required.
- Column_num: This selects the column In array from which to return
Let’s take an example to explain the INDEX function:
In the given dataset, you need to find out the quantity of SHIRT of XL size. To do this task you need to use INDEX function. The steps are as follows:
To illustrate, the formula =INDEX (C5: G9, A7: F3) would return the entry in the third row and the fourth column of the array. this entry is the one in cell F7.
The INDEX MATCH function
The MATCH function returns the relative position of an item in a range that matches a specified value in a
specified order. You use MATCH instead of one of the lookup functions when you need the position of an item in a range instead of the item itself.
The Syntax of the MATCH function:
=MATCH (Lookup _value, Lookup_array, match_type)
The MATCH function takes the following arguments:
- Lookup_value: This is the value you use to find the value you want in a table. Lookup_value can be a value
(i.e. numtær, text or logical value) or a reference to a number, text or logical value.
- Lookup_array: This is a contiguous range of cells that contains possible lookup values.
- Match_type: This is the number -1, O or 1.
- If match_type is 1, MATCH finds the largest value less than or equal to lookup_value. (lookup_array must be in ascending order.).
- If match_type is O, MATCH finds the first value exactly equal to lookup_value.
- If match_type is -1, MATCH finds the smallest value greater than Or equal to lookup_value. (lookup_array
must be in descending order.
If you omit the match_type argument, this argument is assumed to be 1.
In this database, we need to find out the position of Shirt and Size XL Match function returns the position of shirt and size XL.
How to Combining the MATCH and INDEX functions
The MATCH and INDEX functions are often used together to perform l00ckups. the MATCH function returns the relative position of a cell in a range that matches a specified value. Whereas INDEX function picks a value from
a range of data by looking down a specified number of rows and then across a specified number of columns.
Let’s take an example to explain combined MATCH and INDEX function:
In this database, we need to Lookup the quantity in hand as per item name and its size. We already perform this
task in two steps. Now we combine the two functions to perform this task.
Through Match & Index Function you may Lookup data from two way. we may also lookup data from right to left.