Search for a cell in an array formula

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

This should be an easy formula but for some reason I can't come up
with it....

All I want to do is to find the cell address if a certain cell equals
another cell within a range:

A C D E
4 1 7 3
2 9 4
6 11 12
14 15 19

So, if A1 is equal to anything in range(C1:E4) then give me the cell
address which is E2. Any suggestions or hints would be greatly
appreciated. Thanks....
 
Hi Brian!

Try this, entered as an array, CTRL,SHIFT,ENTER:

=ADDRESS(MAX((C1:E4=$A$1)*ROW(C1:E4)),MAX((C1:E4=$A$1)
*COLUMN(C1:E4)))

The only problem with this is if there are more than one
instance of the value in the array. This formula will
return the first instance.

Biff
 
I think that this'll return the last (bottom most right).

The first could be:
=ADDRESS(MIN(IF((C1:E4=$A$1)*ROW(C1:E4)>0,ROW(C1:E4))),
MIN(IF((C1:E4=$A$1)*COLUMN(C1:E4)>0,COLUMN(C1:E4))))

(still ctrl-shift-entered)
 
Brian,

Try this array formula:


=IF(COUNTIF(C1:E4,A1)=0,"None",ADDRESS(MIN(IF(C1:E4=$A$1,
ROW(C1:E4),"")),MIN(IF(C1:E4=$A$1,COLUMN(C1:E4),""))))


This formula will return the first instance.


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--
 
Soo Cheon Jheong said:
Brian,

Try this array formula:


=IF(COUNTIF(C1:E4,A1)=0,"None",ADDRESS(MIN(IF(C1:E4=$A$1,
ROW(C1:E4),"")),MIN(IF(C1:E4=$A$1,COLUMN(C1:E4),""))))


This formula will return the first instance.
Click to expand...

All three formulas work great!!!! This will save our employees a lot
of time having to do a search to see which worksheet it's on and then
doing another search to find out which cell it's in. Thanks for
everyone's help!!!!
 
Back
Top