Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a VLOOKUP that looks up an Account # and Returns a Rep. The Account # field contains historical data (every account including inactive ones). How can I setup a VLOOKUP to take to see pull the rep name by checking each Account #?

Account #       | Email            | Rep Name |   Match Name
12345           |  | Rep Name | Rep Name Match
4564566, 45678  |  | Rep Name |      #N/A
share|improve this question
Do you always want to match on an active count? If so, is the active account always the first one? The last one? If not, what is the maximum number of accounts in a row that might be included in the account number column (for example, the second row has 2)? – chuff May 17 '13 at 17:35
as @chuff said, you need to add more info in your question, at minimum, precise the criteria used to decide if an account is active or not. – P. Obertelli May 17 '13 at 18:55

There isn't a max number of accounts. The lookup would check the rep name against a list of 'Active' accounts (usually in a seperate tab). Some accounts have 5+ account #'s. There isn't a hardcoded max in the export.

share|improve this answer

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.

  Option Explicit

  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 = ", "
     End If
     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
              Exit Function
           End If
        Next i
     Next acct
     AcctRepLookup = CVErr(xlErrValue)                     'no match:( exit with error
  End Function

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.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .