In this tutorial we will learn the Index and Match formula in Advanced Excel with the help of an Example given below:

**Example:**

I have a table that consists of Product Names and the prices of Mumbai and the NCR Region.

Now I want data where ID is in the Middle column then. My formula will be to pick the values from the left side of the data or the right-side data.

**For Example:**

If we choose the location as Mumbai and Product 1 as Product Name, we will pick Product 1 from Mumbai Location and fit into the blank cell to get the answer.

So to get the answer, we will be applying the INDEX and MATCH formula. Follow the steps given below to find out the answer.

1. Write **=INDEX** and press the **tab key**

Now there are 3 parameters **(array, row_num, [column_num])**.

The first parameter **array** means selecting the entire data inside the table

2. Choose the **entire table** and press **F4** along with comma **=INDEX(****$B$18:$D$23,**

The second parameter **row_num** means the row number in the a rray from which to return a value

3. So write **2 =INDEX( $B$18: $D$23,2**

**Note –** Here we are writing 2 because the table is talking about Product 1 which lies in the second Row

The second parameter **[column_num]** means the column number from which to return a value

4. So write **1** and close the bracket **=INDEX( $B$18: $D$23,2,1)**

5. Lastly, press **Enter** and you will get your answer as **4,500** which is the price of Product 1 for Mumbai Region

But if I change the Product Name and Location to get the answer then the answer won’t change because the row_num and col_num inside the INDEX Formula is not changed

So here we will be applying MATCH Formula inside the INDEX Formula to get the answer manually whenever we change the Location and Product Name in the given table

**Note –**

1. The row_num counting should always be from top to bottom

2. The col_num counting should always be from left to right

**For Row_Num**

1. Replace the **row_num** and write **MATCH formula** to reserve their seats MATCH()

Now inside the bracket there are 3 parameters **(lookup_value, lookup_array, [match_type])**

The first parameter **lookup_value** means the value to match in lookup_array.

2. Select the **Product 5** cell **=MATCH(C4,**

The second parameter **lookup_array** means range of cells

3. Select the **Product Name Column from top to bottom** and press F4 **=MATCH(C4,$C$18:$C$23,**

The third parameter **match_type** means 1 =less than 0 = exact match, -1 = greater than

6. Write **0** as exact match and **close** the bracket **=MATCH(C4,$C$18:$C$23,0)**

**For Col_num**

1. Replace the** col_num** and write **MATCH formula** to reserve their seats MATCH()

Now inside the bracket there are 3 parameters **(lookup_value, lookup_array, [match_type])**

The first parameter **lookup_value** means the value to match in lookup_array.

2. Select the **Mumbai** cell **=MATCH(C5,**

The second parameter **lookup_array** means range of cells

3. Select the **headers from left to right** and press F4 **=MATCH(C5,$B$18:$D$18,**

The third parameter **match_type** means 1 =less than 0 = exact match, -1 = greater than

4. Write **0** as exact match and **close** the bracket **=MATCH(C5,$B$18:$D$18,0)**

5. Lastly, whenever you change the name and location **MATCH Formula** counts again which helps in input of **INDEX Formula**

## Leave a Reply