Thanks.

Can this code work as is and does this code work if the order is in text
format?
I put this code in VBA and tried using it, but I get the #NAME? error. It
doesn't seem to recognize the function. The only change I made was using
Locate instead of where_is_it. I've done some simple function code, so maybe
I was being too simplistic and hoping that the code would work as is.

Fred

"Gary''s Student" wrote:

Suppose we have:

1 3 5 5
12 44 7 4
43 33 22 87
56 55 17 6
76 66 121 9
37 77 99 8
1 88 4 5

in A1 thru D7 and 17 in A15

The UDF:

Function where_is_it(r1 As Range, r2 As Range) As String
Dim m As Double
m = r1.Value
where_is_it = r2.Find(What:=m, after:=r2(r2.Count)).Address
End Function


when used like:
=where_is_it(A15,A1:D7)
will return
$C$4
the location of 17
--
Gary''s Student
gsnu200710


"FredL" wrote:

So I have a tab that contains the following data:
Project Order1 Order2 Order3 etc
ABC 123 456 789
DEF 654 951 159

Basically, I want to do a search (similar to ctrl - H) on the tab and look
for Order 159. The main information I want is the Project name. I've got
thousands of line, and hundred of Orders to look up; orders go all the way
to
column IV on a few of them. So I need either a formula (similar to vlookup
or search) or a macro that can look up a cell, search the other tab, and
return either a cell location or, better yet, the project.

Is this possible? Thanks.