Hi Harlan

Can't get that to work.

This portion (both instances):

COUNTIF(OFFSET(tbl,ROW(tbl)-CELL("Row",tbl),0,1),A15)

Evaluates to:

COUNTIF({#VALUE!,#VALUE!,#VALUE!},A15)

So the final result of the formula is #N/A.

Biff

"Harlan Grove" wrote in message
ups.com...
Biff wrote...
Well, you didn't mention that the range had merged cells(they usually
cause nothing but grief, as you're finding out)

I also see in your reply to BJ that you may have multiple occurances.

So, your options are extremely limited. Maybe BJ's macro will solve
your problem.
...

VBA not needed, and merged cells aren't a problem for this. As for
duplicates, either you search first by row then by column or first by
column then by row. For example, searching for 5 in

1 2 3 4
6 7 5 8
9 5 0 1

should the result be 3 (matching the 5 in the 2nd row, 3rd column
first,
so mathcing by row then column) or 7 (matching the 5 in the 2nd column,
3rd row first, so matching by column then row)?

To match by row then column, you could use an array formula like

=CELL("Address",INDEX(Tbl,MATCH(TRUE,COUNTIF(OFFSE T(Tbl,
ROW(Tbl)-CELL("Row",Tbl),0,1,),v)0,0)-1,
MATCH(v,INDEX(Tbl,MATCH(TRUE,COUNTIF(OFFSET(Tbl,
ROW(Tbl)-CELL("Row",Tbl),0,1,),v)0,0),0),0)))

where Tbl represents the data range and v the value to match.