Advanced Excel Index + Match Formula

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