B
Bony Pony
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
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