External reference not working?
 

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

External reference not working?
 

Hi,

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

Thanks,
--
Farhad Hodjat


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

What am I doing wrong?

External reference not working?
 

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


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

What am I doing wrong?

External reference not working?
 

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


"Farhad" wrote in message
...
Hi,

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

Thanks,
--
Farhad Hodjat


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

What am I doing wrong?

External reference not working?
 

That was it. Thank you much!

"Kat" wrote:

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


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

What am I doing wrong?

External reference not working?
 

You are most welcome!
--
Kat


"Todd Lietha" wrote:

That was it. Thank you much!

"Kat" wrote:

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


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

What am I doing wrong?