INDEX with area selection - Not working if areas are on different sheets

[Anshu Bantra]

Hi All,

Look at the below formulas, if use areas on the same sheet, my Index is returning values. However, if I select areas from two sheets it throws error.

What am I doing incorrectly?

 

[pecoflyer]

Perhaps post your sheet instead of a picture which we cannot work on?

 

[GraH - Guido]

Perhaps simply this, in the match part, you do NOT refer to sheet2.

 

[Anshu Bantra]

Perhaps post your sheet instead of a picture which we cannot work on?

I have attached the file, thanks for offering to take a look.

 

[Anshu Bantra]

Perhaps simply this, in the match part, you do NOT refer to sheet2.

Hi Mate,

Match does not have to refer to the second sheet at all. That's not what is causing this error. Thanks for taking a look though.

 

[bosco_yip]

Hi Mate,

Match does not have to refer to the second sheet at all. That's not what is causing this error. Thanks for taking a look though.

1] Extract from MS Excel - INDEX function explanation :

INDEX Reference form

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

Area_num The areas must all be located on one sheet. If you specify areas that are not on the same sheet as each other, it will cause a #VALUE! error.
……..you could use the CHOOSE function to calculate which range will be used.

2] then, try to use :

=INDEX(CHOOSE(MATCH(F12,E1:F1,0),B3:D7,Sheet2!G3:I7),MATCH(F11,CHOOSE(MATCH(F12,E1:F1,0),B3:B7,Sheet2!G3:G7),0),3)

Regards
Bosco

 

[Anshu Bantra]

Hi Bosco

This did the trick. Thanks for providing this solution.

Cheers
Anshu