Sumproduct to return a text

[Chanceuxbp]

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.

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Chanceuxbp]

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]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Chanceuxbp]

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]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk