I have a 2 worksheets:

Sheet 1:
Account No. Profit Center Group
31000 80000000
21000 40000000
30000 85000000

I want to return the Group Name from another worksheet containing wildcard
data:

GROUP Account No Profit Center
A 3*********** 8***********
B 21********** 400********

For e.g the first record in Sheet 1 to return Group A, 2nd record to return
Group B and third record to return Group A.

I try sumproduct but can only return value not text.

Please help.

I think that you are going to have to provide more details of how it is
decided what appears in Sheet 2 eg why 3*********** and 8********* but
21******* & 400********? Additionally I assume that there will be more
account numbers.

For what you posted:
=IF(ISNUMBER(SEARCH(SUBSTITUTE(Sheet2!$B$2,"*","") ,$A$2:$A$4)),"A","B")

and copied down would return A, B & A but I suspect that that s not what you
want.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Chanceuxbp" wrote in message
...
I have a 2 worksheets:

Sheet 1:
Account No. Profit Center Group
31000 80000000
21000 40000000
30000 85000000

I want to return the Group Name from another worksheet containing wildcard
data:

GROUP Account No Profit Center
A 3*********** 8***********
B 21********** 400********

For e.g the first record in Sheet 1 to return Group A, 2nd record to
return
Group B and third record to return Group A.

I try sumproduct but can only return value not text.

Please help.

Yes, there will be more accounts numbers.

The group name to appear on Sheet 1 if both account numbers and profit
center on Sheet 1 match both account numbers and profit center on Sheet 2.

3***** means account number starting with 3 and 8***** means any profit
centers starting with 8 will meet the conditions to return the Group Name on
Sheet2 to Sheet1 Column C(Group)

21****** means account number starting with 21 and 400**** means any profit
centers starting with 400 will meet the conditions to return the Group Name
on Sheet2 to Sheet 1 Column C(Group).

Hope that I am clearer now.

Thank you.

"Sandy Mann" wrote:

I think that you are going to have to provide more details of how it is
decided what appears in Sheet 2 eg why 3*********** and 8********* but
21******* & 400********? Additionally I assume that there will be more
account numbers.

For what you posted:
=IF(ISNUMBER(SEARCH(SUBSTITUTE(Sheet2!$B$2,"*","") ,$A$2:$A$4)),"A","B")

and copied down would return A, B & A but I suspect that that s not what you
want.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Chanceuxbp" wrote in message
...
I have a 2 worksheets:

Sheet 1:
Account No. Profit Center Group
31000 80000000
21000 40000000
30000 85000000

I want to return the Group Name from another worksheet containing wildcard
data:

GROUP Account No Profit Center
A 3*********** 8***********
B 21********** 400********

For e.g the first record in Sheet 1 to return Group A, 2nd record to
return
Group B and third record to return Group A.

I try sumproduct but can only return value not text.

Please help.

What I was meaning was if 31000 is in the same group as 30000 why would
21000 not be in the same 20000? Or if it is why 21*******? why not just
2********?

You say that there are more account numbers, are there more Groups?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Chanceuxbp" wrote in message
...
Yes, there will be more accounts numbers.

The group name to appear on Sheet 1 if both account numbers and profit
center on Sheet 1 match both account numbers and profit center on Sheet 2.

3***** means account number starting with 3 and 8***** means any profit
centers starting with 8 will meet the conditions to return the Group Name
on
Sheet2 to Sheet1 Column C(Group)

21****** means account number starting with 21 and 400**** means any
profit
centers starting with 400 will meet the conditions to return the Group
Name
on Sheet2 to Sheet 1 Column C(Group).

Hope that I am clearer now.

Thank you.

"Sandy Mann" wrote:

I think that you are going to have to provide more details of how it is
decided what appears in Sheet 2 eg why 3*********** and 8********* but
21******* & 400********? Additionally I assume that there will be more
account numbers.

For what you posted:
=IF(ISNUMBER(SEARCH(SUBSTITUTE(Sheet2!$B$2,"*","") ,$A$2:$A$4)),"A","B")

and copied down would return A, B & A but I suspect that that s not what
you
want.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Chanceuxbp" wrote in message
...
I have a 2 worksheets:

Sheet 1:
Account No. Profit Center Group
31000 80000000
21000 40000000
30000 85000000

I want to return the Group Name from another worksheet containing
wildcard
data:

GROUP Account No Profit Center
A 3*********** 8***********
B 21********** 400********

For e.g the first record in Sheet 1 to return Group A, 2nd record to
return
Group B and third record to return Group A.

I try sumproduct but can only return value not text.

Please help.

Hi Sandy, yes there are more account numbers and Group. The following is an
extract. There would be account 22000 that falls into another Group
22*******. That's why I have 210000 in the Group 21******.

Basically, it is to use lookup value (Account+Profit Center) in Sheet1 with
lookup array in Sheet2 to return a Group Name to Sheet1 column C.

Please help.

Thank you.

"Sandy Mann" wrote:

What I was meaning was if 31000 is in the same group as 30000 why would
21000 not be in the same 20000? Or if it is why 21*******? why not just
2********?

You say that there are more account numbers, are there more Groups?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Chanceuxbp" wrote in message
...
Yes, there will be more accounts numbers.

The group name to appear on Sheet 1 if both account numbers and profit
center on Sheet 1 match both account numbers and profit center on Sheet 2.

3***** means account number starting with 3 and 8***** means any profit
centers starting with 8 will meet the conditions to return the Group Name
on
Sheet2 to Sheet1 Column C(Group)

21****** means account number starting with 21 and 400**** means any
profit
centers starting with 400 will meet the conditions to return the Group
Name
on Sheet2 to Sheet 1 Column C(Group).

Hope that I am clearer now.

Thank you.

"Sandy Mann" wrote:

I think that you are going to have to provide more details of how it is
decided what appears in Sheet 2 eg why 3*********** and 8********* but
21******* & 400********? Additionally I assume that there will be more
account numbers.

For what you posted:
=IF(ISNUMBER(SEARCH(SUBSTITUTE(Sheet2!$B$2,"*","") ,$A$2:$A$4)),"A","B")

and copied down would return A, B & A but I suspect that that s not what
you
want.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Chanceuxbp" wrote in message
...
I have a 2 worksheets:

Sheet 1:
Account No. Profit Center Group
31000 80000000
21000 40000000
30000 85000000

I want to return the Group Name from another worksheet containing
wildcard
data:

GROUP Account No Profit Center
A 3*********** 8***********
B 21********** 400********

For e.g the first record in Sheet 1 to return Group A, 2nd record to
return
Group B and third record to return Group A.

I try sumproduct but can only return value not text.

Please help.

Would a User Defined Function do? If so with the list of Account No's &
Profit Center's in A1:B6, the list of Groups, Account No's and Profit
Centers (both with ******), in G1:I5, I created another table in K1:L5,
(labels in K1&L1) with the formula:
=SUBSTITUTE(H2,"*","")
and copied down & across.

The UDF in a normal module is:

Option Explicit
Function FindIt(Acc, Ctr)
Dim x As Long
Dim Here As String

Application.Volatile

For x = 2 To 5 'Change to suit your list
If Left(Acc, Len(Cells(x, 11).Value)) = _
Cells(x, 11) Then

If Left(Ctr, Len(Cells(x, 12).Value)) = _
Cells(x, 12) Then
Here = Cells(x, 7).Value
Exit For
End If

End If
Next x

FindIt = Here

End Function

Then in C2 I entered the formula:

=FindIt(A2,B2)


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Chanceuxbp" wrote in message
...
Hi Sandy, yes there are more account numbers and Group. The following is
an
extract. There would be account 22000 that falls into another Group
22*******. That's why I have 210000 in the Group 21******.

Basically, it is to use lookup value (Account+Profit Center) in Sheet1
with
lookup array in Sheet2 to return a Group Name to Sheet1 column C.

Please help.

Thank you.

"Sandy Mann" wrote:

What I was meaning was if 31000 is in the same group as 30000 why would
21000 not be in the same 20000? Or if it is why 21*******? why not just
2********?

You say that there are more account numbers, are there more Groups?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Chanceuxbp" wrote in message
...
Yes, there will be more accounts numbers.

The group name to appear on Sheet 1 if both account numbers and profit
center on Sheet 1 match both account numbers and profit center on Sheet
2.

3***** means account number starting with 3 and 8***** means any profit
centers starting with 8 will meet the conditions to return the Group
Name
on
Sheet2 to Sheet1 Column C(Group)

21****** means account number starting with 21 and 400**** means any
profit
centers starting with 400 will meet the conditions to return the Group
Name
on Sheet2 to Sheet 1 Column C(Group).

Hope that I am clearer now.

Thank you.

"Sandy Mann" wrote:

I think that you are going to have to provide more details of how it
is
decided what appears in Sheet 2 eg why 3*********** and 8*********
but
21******* & 400********? Additionally I assume that there will be
more
account numbers.

For what you posted:
=IF(ISNUMBER(SEARCH(SUBSTITUTE(Sheet2!$B$2,"*","") ,$A$2:$A$4)),"A","B")

and copied down would return A, B & A but I suspect that that s not
what
you
want.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Chanceuxbp" wrote in message
...
I have a 2 worksheets:

Sheet 1:
Account No. Profit Center Group
31000 80000000
21000 40000000
30000 85000000

I want to return the Group Name from another worksheet containing
wildcard
data:

GROUP Account No Profit Center
A 3*********** 8***********
B 21********** 400********

For e.g the first record in Sheet 1 to return Group A, 2nd record to
return
Group B and third record to return Group A.

I try sumproduct but can only return value not text.

Please help.