(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?

.. 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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Todd Lietha" wrote:
(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?