Formula understanding

[neeraj2050]

Greetings!!

I found following formula

=INDEX(table1,MATCH($B9,INDEX(table1,,1),0),MATCH(AJ$5,INDEX(table1,1,),0)),0,””)& “”

May I know, the part of the formula highlighted with pink color. I want to understand it.

Regards
Neeraj Kumar Agarwal

 

[bosco_yip]

=INDEX(table1,MATCH(criteria1,INDEX(table1,,1),0),MATCH(criteria2,INDEX(table1,1,),0))

Assume "table1": A1:F6, "criteria1": H2 and "criteria2": I2

In J2, enter formula :

=INDEX(A1:F6,MATCH(H2,INDEX(A1:F6,,1),0),MATCH(I2,INDEX(A1:F6,1,),0))

>>

=INDEX(A1:F6,MATCH(H2,{0;"X1";"X2";"X3";"X4";"X5"},0),MATCH(I2,{0,"Y1","Y2","Y3","Y4","Y5"},0))

>>

=INDEX(A1:F6,3,4)

>>

=4

Regards
Bosco

 

[neeraj2050]

@Bosco Sir : Thanks!! But I could not figure out INDEX(A1:F6,,1)
INDEX(A1:F6,1,)

Firstly, why have we used ,,
Secondly, we have used , after 1

 

[bosco_yip]

@Bosco Sir : Thanks!! But I could not figure out INDEX(A1:F6,,1)
INDEX(A1:F6,1,)
Firstly, why have we used ,,
Secondly, we have used , after 1

INDEX(table1,,1)…...INDEX(table1,1,)

INDEX Syntax :

INDEX(array, row_num, [column_num])

1] INDEX(table1,,1) is equal to INDEX(table1,0,1)

1.1] INDEX(table1,0,1)

and, in post #.2 example, "table" =A1:F6

1.2.] INDEX(table1,0,1)

=INDEX(A1:F6,0,1) >>

herein 1 stand for 1st column num and return >>

={0;"X1";"X2";"X3";"X4";"X5"}

2] Similar to INDEX(table1,1,)

2.1.] INDEX(table1,1,0)

=INDEX(A1:F6,1,0) >>

herein 1 stand for 1st row num and return >>

={0,"Y1","Y2","Y3","Y4","Y5"}

Regards
Bosco

 

[pecoflyer]

To add a little to Bosco's explanation, when embedded in a formula, INDEX returns a cell or range reference. Very useful for dynamic ranges when one wants to avoid volatile functions like OFFSET

 

[neeraj2050]

@ Bosco Sir: Thanks a lot!!
@ Pecoflyer : Thanks for your input!!