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:

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.

- If

### Some of the Important things about MATCH Formula:

- It is not case-insensitive. It does not know the difference between upper and lower case.
- If the Match formula cannot find any matches, it results into #N/A error.
- 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 - Match function does not return the matching string, it only returns the relative position of that string.
- 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

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

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

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:

- Array form
- Reference form

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

In the above example, we are trying to get the value from the 2^{nd} row and 2^{nd} 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:

In the above example, we are trying to get the value from the 4^{th} row and 1^{st} 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:

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!**

- Learn all about the new XLOOKUP Function in Excel! - 2019-09-27
- FOR EACH Loop in Excel VBA - 2018-12-15