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