Index function problems when multiple tables/area numbers are on different sheets

[DavidSCowan]

I want to use the reference form of the Index function where there are different lookup tables/area numbers but where one of the tables is on a different sheet. I have a simplified example where this works no problem when the areas are on the same sheet but doesn't work if one of the areas is on a different sheet. So in the first case we have:

INDEX((C24:CF28,C33:F37,C42:F46),2,2,2) which is looking up the contents of the 2nd row/2nd column of the second table. This works fine.

But if the second table is on another sheet this doesn't work

INDEX((C24:CF28,Sheet1!C33:F37,C42:F46),2,2,2) produces a #Value! error.

Can someone help please

David

 

[XLearner]

Hi,

If not too late. Possible workaround:

=INDEX(CHOOSE(2,C24:CF28,Sheet1!C33:F37,C42:F46),2,2) where 2 "is the equivalent" of the Area number in your INDEX((C24:CF28,Sheet1!C33:F37,C42:F46),2,2,2).

CHOOSE function can return a Reference

Regards
XLearner