Did you array enter the formula? Array formulas need to be entered using the
key combination of CTRL,SHIFT,ENTER (not just ENTER)

Here's another way to do this that doesn't require an array formula:

="A"&INDEX(ROW(table),MATCH(A22,table,0))&"-A"&LOOKUP(A22,table,ROW(table))

--
Biff
Microsoft Excel MVP


"blumonkey" wrote in message
...
Version:Excel 2003
I am trying to reference the first and the last cell within a group of
cell with the same value.

Here is what I have:
B22=ADDRESS(MIN(IF(table=A22,ROW(table))),MIN(IF(t able=A22,COLUMN(table))),,
1) & "-" &
ADDRESS(MAX(IF(table=A22,ROW(table))),MAX(IF(table =A22,COLUMN(table))),,
1)

Where table is A27:A10377
A22 = "REF VALUE"

The cell B22 returns #VALUE, however when I use the formula button
(fx), the formula result is displayed as $A$34-$A$97 (which is
correct).

When I evaluate the formula, I get and error #value
ADDRESS(MIN(IF(#value!=A22, .....
or in other words: IF(A27:A10377...

I even tried to change the cell reference to a number (A22=60155),
thinking it was something with the MIN MAX functions, but alas, the
same result.

If anyone can help, or explain why this doesn't work I would greatly
appreciate it.

Thank you
Ivar