While there are several ways to get this done using built-in Excel capabilities or formulas, none are particularly appealing.
The basic idea would be to split into different cells the "Account #" items that actually include several accounts, do a separate rep name lookup for each of those included accounts, and then pick out the successful lookup(s), if any. The split could be done using the Text-to-Columns command in the Data tab or using a set of string-parsing formulas. The "picking out" of successful lookups could be done with nested
IF(ISERROR(...)) functions or maybe a more exotic array formula.
A VBA solution is a sensible alternative here.
The following user-defined function takes as its arguments a string (or cell reference to a string) that includes one or several accounts and a reference to a lookup range; it returns a rep name if there is a match on an account in the string. An optional third argument can be used to specify the separator between accounts in the account string. If none is specified, the function uses a default separator of a comma and space (", ").
The function will return the rep name for the first account match it finds, ignoring any matches that might be found for other accounts in the account string. If no account match is found, the function returns a #VALUE! error.
Function AcctRepLookup(Accts As String, RepLookUpRange As Range, Optional Separator As Variant) As Variant
Dim acctArray As Variant
Dim acct As Variant
Dim lookupArray As Variant
Dim i As Long
If IsEmpty(Separator) Then
Separator = ", "
acctArray = Split(Accts, Separator) 'put acct list into array
lookupArray = RepLookUpRange.Value 'put lookup table into array
For Each acct In acctArray 'loop through accounts
For i = LBound(lookupArray) To UBound(lookupArray) 'loop through lookup table
If UCase(acct) = UCase(lookupArray(i, 1)) Then
AcctRepLookup = lookupArray(i, 2) 'a match:) exit with rep name
AcctRepLookup = CVErr(xlErrValue) 'no match:( exit with error
To install the function in the workbook you are using, first select the Visual Basic option (first item on the left) on the Developer tab of the Ribbon and then choose Insert / Module from the menu bar Paste the function into the code pane that appears.