External reference not working?

[Guest]

(Excel 2003) Using INDEX, SMALL, and ROW functions in a formula per the
examples in artical "How to look up a value in a list and return multiple
corresponding values". When I use the example verbatim, everything works
fine. But when I change the parameter for the comparison value to retrieve
from a different sheet in the same workbook, the formula no longer finds a
match.

example:
=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))

changed to:
=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=TL!$C$2,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=TL!$C$2,ROW($A$1:$A$7)),ROW(1:1)),2))

The value in the referenced cell is identical to the value in the local
cell.

What am I doing wrong?

 

[Guest]

Hi,

May be you should put 'TL'! instead TL!

Thanks,

 

[Guest]

The formula uses an array; reset the array using F2 followed by (Ctrl +
Shift)+Enter. That should restore your functionality.

 

[Peo Sjoblom]

While it is always a good design to add the apostrophes regardless it is not
necessary if there are no spaces in the sheet name


--


Regards,


Peo Sjoblom

 

[Guest]

That was it. Thank you much!

The formula uses an array; reset the array using F2 followed by (Ctrl +
Shift)+Enter. That should restore your functionality.

 

[Guest]

You are most welcome!
--
Kat

That was it. Thank you much!