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.

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.

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.

That particular error means that Excel cant find the function. It must go
in a standard module:


Functions are very easy to install and use:

1. CNTRL-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the function will be saved with it.


To remove the function:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about functions in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

If you have any other problems, just update this post and we will work them
out.


--
Gary''s Student
gsnu200710


"FredL" wrote:

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.

Great, I finally got it to work. Not sure why, but I did exactly what I did
yesterday, and now it works. Thanks a million.

btw, in my system (PC) I used alt+f11 to get to the VBE Window, Alt+T,M,V to
achieve the same as above. When I did the ctrl+f11, a tab named Macro
appeared

"Gary''s Student" wrote:

That particular error means that Excel cant find the function. It must go
in a standard module:


Functions are very easy to install and use:

1. CNTRL-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the function will be saved with it.


To remove the function:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about functions in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

If you have any other problems, just update this post and we will work them
out.


--
Gary''s Student
gsnu200710


"FredL" wrote:

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.

If data is in A1:D10 and lookup value is in K1 say, try:

=INDEX(A$1:A$10,MIN(IF(A$1:H$10=K1,ROW(A$1:H$10))) )

executed with ctrl+shift+enter as it's an array formula.


On 15 Mar, 23:08, 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.

Hi Lori,

I tried this as well, but got the #Value! error. I'm wondering if the IF
statement is causing it or not.

Fred

"Lori" wrote:

If data is in A1:D10 and lookup value is in K1 say, try:

=INDEX(A$1:A$10,MIN(IF(A$1:H$10=K1,ROW(A$1:H$10))) )

executed with ctrl+shift+enter as it's an array formula.


On 15 Mar, 23:08, 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.