Return cell address of a cell based on contents of cell.

[Danny]

I have a list (sheet1) of about 300 items (a1:a300) that appear in random
locations on sheet 2 of my workbook (a1:cf13). I want to find the items in
my list a1:a300 on sheet 2 and return their location (cell address) to
b1:b300.

If you know whether or not this can be done I would greatly appreciate it.
I have searched the forum for something similar to this but cant find what I
am looking for. I found a couple that use the address function with the max
function but that seems to just be for a single row or column. I am trying to
search through a range about 13x83.

 

[T. Valko]

As long as each item is unique.

Try this array formula** :

=ADDRESS(MAX((Sheet2!A$1:CF$13=A1)*ROW(Sheet2!A$1:CF$13)),MAX((Sheet2!A$1:CF$13=A1)*COLUMN(Sheet2!A$1:CF$13)),4)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Copy down as needed

If there are duplicate items then it becomes a nightmare!

 

[Shane Devenshire]

Hi,

This is a perfect candidate for using range names. Name the range A1:CF13 D
Then the formula simplifies to

=ADDRESS(MAX(ROW(D)*(A1=D)),MAX(COLUMN(D)*(A1=D)))

still array entered. Notice no need for sheet references, no need for
absolute notation!

Cheers,
Shane Devenshire

 

[Danny]

Thank you both. This is really helpful. I figured that if all the numbers
were not unique then it would make it more difficult, that is why I made a
point of mentioning that. Also, the named range makes it a lot simpler
function to check if something goes wrong. I noticed one difference in the
two (the 4) at the end so i tried both and realized that changed the result
from $e$4 to e4.

But thank you both again.

 

[T. Valko]

You're welcome. Thanks for the feedback!