Prev: Sum Data if Column B = Q
Next: Write a Formula
From: Bony Pony on 13 Feb 2010 16:02
Hi everyone,
21:00 on a Saturday night and I'm struggling with this!

I have textual data in cells C158:L177

Unfortunately these are not all unique values ...

I want to get the address of each dearched instance.

In cell C179 I want to enter a search text which exists in the data.

In D179 I want the address of the first instance, E179 the address of the
second etc.

For unique values the formula:
=ADDRESS(SUMPRODUCT(--($C$158:$L$177=C179)*ROW($C$158:$L$177)),SUMPRODUCT(--($C$158:$L$177=C179)*COLUMN($C$158:$L$177)),4)

works like a dream but for multiple instances, it doesn't work.

I can do this with VBA in a heartbeat but I am determined to do it with a
non array entered function.

Please can anyone hekp?

Kind regards,
Bony

--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."
 | 
Pages: 1
Prev: Sum Data if Column B = Q
Next: Write a Formula