What function would tell me the cell the information is in?

[ak_edm]

Hi,

As an example to a larger project I'm working, I have two columns of data I
want to search for an entry, "dogs" for example. And I'd like a function
that returns the cell location (or row, column is fine too) in which "dogs"
has been found. I know lookup, match, offset, not very well, and I'm having
trouble when the search area is more then one row AND one column in
dimensions.

As a simpler example, set up a spreadsheet like this :

Column A Column B Column C
Row
1 1 6 8
2 2 7 B3
3 3 8
4 4 9
5 5 10

The numbers 1 - 10 are entered in two columns between rows 1 and 5. I enter
the number I want to locate into cell C1 (I enter "8"). The function in cell
C2 returns the call reference "B3" meaning that the value "8" was found at
location B3. If I entered "4", then the result would be "A4".

What function do I use?
Thanks.


- Eric
Anchorage, Alaska

 

[T. Valko]

Based on your posted sample, try this array formula** :

C1 = 8

=ADDRESS(MAX((A1:B5=C1)*ROW(A1:B5)),MAX((A1:B5=C1)*COLUMN(A1:B5)),4)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.