reporting back a cell location?

[mitcheroo]

A 6-column by 50-row range (a1:f50) is filled with random numbers. The
numbers are from 1 to, say, 100.

What Excel function, written in a serarate cell, will return the cell
location of the first occurence of a given number as we read the cells
from left to right and from top to bottom in the range?

(e.g., "73" occurs in cells D32, A56, B88 and F97. D32 would be the
'earliest occurence'. What operator, function or formula would report
back "D32"?)

From a college class a long time ago, I seem to recall another kind of

spreadsheet that could do that.

 

[Ragdyer]

You said your range was A1 to F50, then your examples of the locations of
"73" was for a different range.

Try this *array* formula which I sized for your original A1 to F50.
Enter the number to find in G1.

=ADDRESS(MAX((A1:F50=G1)*ROW(A1:F50)),MAX((A1:F50=G1)*COLUMN(A1:F50)),1)
--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

This will return an absolute address.
If you wish a relative address to display, change the final 1 in the formula
to a 4.

 

[Herbert Seidenberg]

If 73 was located at C50 and F23
then the formula would return F50

 

[Dave Peterson]

I'd use a couple of helper cells just to keep the formula from becoming too
massive.

I put the number I wanted to find in G1.

I put this in H1:
=MIN(IF(A1:F50=G1,ROW(A1:F50)))

I put this in I1:
=MIN(IF(INDEX(A1:F50,H1,)=G1,COLUMN(A1:F1)))

Both of these are array formulaa. Hit ctrl-shift-enter instead of enter.
If you do it correctly, excel will wrap curly brackets {} around your formula.
(don't type them yourself.)

Then I put this in J1:
=ADDRESS(H1,I1)

If you want to drop the helper columns, you could use a formula like:

=ADDRESS(MIN(IF(A1:F50=G1,ROW(A1:F50))),
MIN(IF(INDEX(A1:F50,MIN(IF(A1:F50=G1,ROW(A1:F50))),)=G1,COLUMN(A1:F1))))

It's still an array formula and G1 contains the value to find.

 

[Jim May]

A macro is easier; In your worksheet code module put:
In cell J2 enter your desired search #.

Sub foo()
Set rng = Range("A1:F50")
With rng.Cells
Set s = .Find(Range("J2"), LookIn:=xlValues)
If Not s Is Nothing Then
firstaddress = s.Address
MsgBox "Found it First in Cell " & firstaddress
Else
MsgBox "The Number cannot be found"
End If
End With
End Sub

HTH

 

[RagDyer]

So ... it gave the max Row of one, and the max Column of the other.

I guess the name for that would be "Inadequate Testing".<g>