How to do index match multiple criteria in excel

How to do index match multiple criteria in excel

How Match and Index Work?

The INDEX-MATCH method is the most powerful and flexible way to look up data in Excel. It relies on two Excel functions: The INDEX function allows you to return the entry within a number array in any row and column. The Excel Index function returns a reference to a cell in a specified row and column of a user-supplied range of cells. The most commonly used syntax for the INDEX function is:
=INDEX(reference, row _ num, column_num)

  1. Array: This is a range of cells or a constant array. If the array contains only one row or column, optional.
  2. The row number: is the corresponding row number or column number argument: this selects the row in the array from which to return a value. If row num is omitted, column num is required.
  3. Column number: This selects the column from which to return.

Let’s take an example to explain the INDEX function:

You need to find out the quantity of XL size SHIRT in the given dataset. You need to use the INDEX function to do this task.

The steps are as follows: the formula= INDEX (C5: G9, A7: F3) would return the entry in the third row and the fourth column of the array to illustrate. This entry is in cell F7.

The INDEX MATCH function

The MATCH function returns an item’s relative position 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 an item’s position 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

MATCH and INDEX functions are often used together to perform l00ckups. The MATCH function returns the cell’s relative position within a range that matches the specified value. Whereas the INDEX function selects a value from a data range by looking down a specified number of rows and then across a specified number of columns.

Let’s take an example to explain the combined MATCH and INDEX function:
In this database, we need to look at the quantity in hand according to the name of the item and its size. We are already performing this task in two steps. Now we are combining 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.


How to use different type of lookup function in ms excel

Previous article

How to use dynamic range VLookup function in ms excel 2016

Next article

You may also like


Leave a reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.