Cell reference question

[blumonkey]

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(table=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

 

[T. Valko]

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))

 

[T. Valko]

Correction:

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

Should be:

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

 

[blumonkey]

Correction:


Should be:

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

The Array enter was the thing I was missing, didn't even know about
it Like your solution better though, just to help me understand
(if you have the time), so if I use the LOOKUP and the ROW in a
combination, it would look up the last value found of A22?
Is INDEX something similar to ADDRESS, but list the row only?

Thank you again!
Ivar

 

[T. Valko]

The Array enter was the thing I was missing, didn't even know about
it Like your solution better though, just to help me understand
(if you have the time), so if I use the LOOKUP and the ROW in a
combination, it would look up the last value found of A22?
Is INDEX something similar to ADDRESS, but list the row only?

Thank you again!
Ivar

Yes, use the "corrected" version.

Sort of. You give INDEX a range of values to "hold". Those values (in this
case) are the row numbers for your named range Table. We then tell INDEX we
want the row number that corresponds to the first location of "REF VALUE" by
using the MATCH function.