Return cell address of a cell based on contents of cell.
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!
--
Biff
Microsoft Excel MVP
"Danny" wrote in message
...
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.
|