Find a value in a range then return the cell address

Hello everyone,

Would you please help me to construct a formula that finds a value in a range , then returns the cell address of that value.

Say, i wanted to find the amount 12385 from another sheet , range C2:AA12 (contains only numbers , no duplicates). result should give me the address of that value.

I have tried the address & match function but gives me an N/A error.

Thanks in advance.

Hi,

Since you say that there are no duplicates:

=ADDRESS(SUMPRODUCT((C2:AA12=12385)*(ROW(C2:AA12))),SUMPRODUCT((C2:AA12=12385)*(COLUMN(C2:AA12))),4)

Regards

Originally Posted by XOR LX

Hi,

Since you say that there are no duplicates:

=ADDRESS(SUMPRODUCT((C2:AA12=12385)*(ROW(C2:AA12))),SUMPRODUCT((C2:AA12=12385)*(COLUMN(C2:AA12))),4)

Regards

Works perfect!
Thanks so much

here is my formula

Please Login or Register  to view this content.

You're welcome, and thanks for the feedback!