Advanced-Excel

How to do index match multiple criteria in excel

0
How to do index match multiple criteria in excel

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)

  1. 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.
  2. Row _ num: This selects the row in the array from which to return a value. If row_num is omitted, column_num is required.
  3. 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.

 


Subhendu Bera
I am Subehndu Bera From India I love to write on a web. always looking for something new to learn. interested in technology and science, and right now I will be pursuing a Bcom in Kolkata.

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

Comments

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.