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, so it should all work, not?

 

[Guest]

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

Possibly not, due to presence of "invisible" extra white spaces
(leading/trailing/in-between).

Try this simple test. Enter in an empty cell: =A10=TL!C2
Does it return TRUE?

Perhaps try wrapping TRIM around TL!C2
Use: TRIM(TL!$C$2) in the expression to replace: TL!$C$2

Or, it could also be a case of a text number in TL!C2
being compared to real numbers in $A$1:$A$7
Use: TL!$C$2+0 to replace: TL!$C$2 in the expression