External reference not working?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
The formula uses an array; reset the array using F2 followed by (Ctrl +
Shift)+Enter. That should restore your functionality.
 
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
 
That was it. Thank you much!

Kat said:
The formula uses an array; reset the array using F2 followed by (Ctrl +
Shift)+Enter. That should restore your functionality.
Click to expand...
 
Back
Top