Most of the times the INDEX and the MATCH functions are used together or referred to together, this makes it difficult to understand their functionality and usage. To get a better understanding of these functions, firstly let’s try to digest the functioning of each of these functions individually. Let’s start with the relatively simpler one, the MATCH function.

MATCH function

The MATCH function searches for a specified item in a range of cells and then returns the position of that item in the range.

Let’s understand how this works with the following example:

MATCH Example

In the above example, we are trying to lookup Ginger from the array $A$2:$A$14 and the value returned would be 5.

The syntax for MATCH is:

MATCH(lookup_value, lookup_array, [match_type])

  • lookup_value – The value that you want to match in lookup_array. The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
  • lookup_array – The range of cells being searched.
  • match_type    This is an optional argument and can be -1, 0, or 1. The default value for this argument is 1. Use 0 for an exact match.
    • If match_type has a value 1, it means that match function will find the largest value that is less than or equal to lookup_value. It can only be applied if the lookup_array is sorted in an ascending order.
    • If match_type has a value 0, it means that match function will find the first value that is equal to the lookup_value. In this case sorting of lookup_array is not important.
    • If match_type’ has a value -1, it means that match function will find the smallest value that is greater than or equal to lookup_value. It can only be applied if the lookup_array is sorted in a descending order.

Some of the Important things about MATCH Formula:

  1. It is not case-insensitive. It does not know the difference between upper and lower case.
  2. If the Match formula cannot find any matches, it results into #N/A error.
  3. Match function supports the use of wildcard operators, but they can only be used in case of text comparisons where the ‘match_type’ is 0.
    * matches any sequence of characters
    ? matches any single character
  4. Match function does not return the matching string, it only returns the relative position of that string.
  5. If the array is not sorted in the ascending order for ‘match_type’ 1 then it results into a #N/A error. Similarly, #N/A error also occurs if the defined cell range is not sorted in descending order for ‘match_type’ equal to -1.

Few Examples:

Let’s take a few examples to understand the various scenarios we have discussed so far and how they work. Please note that in the following examples we are using cell reference to get the lookup_value from a cell, we had used an absolute value (not a cell reference/address) in the previous Example.

Example 1: To find position of a number absent in an array of numbers sorted in ascending order with match_type 1

Match Example 1

 

In the above example, we are finding 20 referenced from the cell D2 in the array A2:A1, with match_type 1. This looks for the number less than or equal to 20 from the array sorted in the ascending order and returns the position of that number, in this case, looks for 19 which is located at position 2.

Example 2: To find position of a number absent in an array of numbers sorted in ascending order with match_type 0

Match Example 2

In the above example, we are finding 20 referenced from the cell D2 in the array A2:A1, with match_type 0 which signifies exact match. This tries to search for 20 from the array, as 20 is not present in the array it throws an error.

Example 3: To find position of a number absent in an array of numbers sorted in descending order with match_type -1

Match Example 3

In the above example, we are finding 31 referenced from the cell D2 in the array A2:A1, with match_type -1. This looks for the number greater than or equal to 31 from the array sorted in the descending order and returns the position of that number, in this case, looks for 32 which is located at position 4.

INDEX Function

Since we know how the MATCH functions, let’s look at the functionality of INDEX. The use of INDEX is to fetch the data from a cell or a range of cells given the position of the cell within a range (an array) or multiple ranges (multiple arrays). In simple words, you use INDEX when you know (or can calculate) the position of an element in a range and you want to get the actual value of that element.

There are two versions of the INDEX function in Excel:

  1. Array form
  2. Reference form

Let’s take an example and understand how the array form of the function works:

Index Example 1

In the above example, we are trying to get the value from the 2nd row and 2nd column of the highlighted region.

The Syntax for Array form:

INDEX(array, row_num, [column_num])

The array form of INDEX function has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.).

  • Array – A range of cells or an array constant. If the array contains only one row or column, the corresponding Row_num or Column_num argument is optional. If array has more than one row and more than one column, and only Row_num or Column_num is used, INDEX returns an array of the entire row or column in array
  • Row_num – Selects the row in the array from which to return a value. If Row_num is omitted, Column_num is required.
  • Column_num – Selects the column in the array from which to return a value. If Column_num is omitted, Row_num is required.

Let’s take an example and understand how the reference form of the function works:

Index Example 2

In the above example, we are trying to get the value from the 4th row and 1st column of Area 2 highlighted in green.

In the reference form of INDEX, the first parameter is a reference, which is supplied as a reference to one or more cell ranges (Note: If more than one range is used in the reference, it should be comma separated and enclosed within a set of parenthesis). It returns the data of the cell at the intersection row_num and col_num.

The Syntax for the reference form:

INDEX (reference, row_num, [col_num], [area_num])

The reference form of INDEX has the following parameters:

  • Reference – One or multiple ranges from which the data needs to be pulled
  • Row_num – Selects the row in the array from which to return a value. If Row_num is omitted, Column_num is required.
  • Column_num – Selects the column in the array from which to return a value. If Column_num is omitted, Row_num is required.
  • Area_num – If the reference is supplied as multiple ranges, area_num indicates the range from which the data needs to be pulled. IT is supplied as a number. For example, in the formula =INDEX((A1:CD6,A8:D10),2,2,2), area_num is supplied as 2, which refers to the range A8:D10.

I’ll discuss the reference form of INDEX in detail in another article. Now, we’ll see how array form can be combined with MATCH.

Combination of INDEX and MATCH

Now that we have understood INDEX and MATCH functions individually, let’s try and understand how it can be used together to perform a lookup. For this purpose, we’ll have to use MATCH function inside INDEX. This also helps to make the look up dynamic. We can use the column headers and row identifiers inside the MATCH function, then use the result of match as positions for INDEX

Consider the following example to understand how it works:

Index Example 3

In the above example, we are trying to get “Quantity of Bookcases sold”. The MATCH function is used to get the position of Bookcases in the Product Sub-Category column and Quantity in the first row(or the header of the table). Then, with the positions identified INDEX is used to get the desired result.

 

Please share

Did you find this useful?  Why not share the love and tell your friends on LinkedIn, Google+, Twitter and Facebook.

Want more tips like this? Sign up for our newsletter and receive Excel tips & tricks + Exclusive Udemy discounts to your inbox. You will also get our Excel Shortcuts cheat sheet for free!

Get Free Email Updates!

Signup now and receive an email once I publish new content.

I agree to have my personal information transfered to ConvertKit ( more information )

I will never give away, trade or sell your email address. You can unsubscribe at any time.

Abdelrahman Abdou
Latest posts by Abdelrahman Abdou (see all)
It's only fair to share...Share on Facebook
Facebook
0Pin on Pinterest
Pinterest
0Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin