Dear all,

I have a list of integers from A1 to A10. If I would like to store the
absolute address of the third cell counted from the top in column A
containing "1" in B1, what formula should I use?

For example, if the data from A1 to A10 are 3, 1, 1, 2, 1, 2, 2, 2, 1,
3, I want B1 stores "$A$5" as A5 is the third cell containing "1". Thanks in
advance.

Best Regards,
Chris

You need an array formula for this: type this formula

=ADDRESS(SMALL(IF(A1:A10=B1,ROW(A1:A10),65536),3), 1)

then press CTRL+SHIFT+ENTER, not just ENTER

On Thu, 4 Nov 2004 03:44:03 +0800, "Chris" wrote:

3, 1, 1, 2, 1, 2, 2, 2, 1,
3