0

I have an project billing report downloaded with First Name in column in A and Charge Code in Column B. There are multiple charges codes in one cell which determine whether it is billable or non billable or an erroneous charge code entry.

I am having difficulty using this index match formula as below. Is there a way to lookup value from a cell which has multiple values separated by commas?

=INDEX(H2:H4,MATCH(H2,B2:B4,0))

Excel Billing example:

enter image description here

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Zain
  • 7
  • 3

2 Answers2

0

Updating answer after seeing the sheet layout ... perhaps something like this (with match bug corrected) ...

Option Explicit

Public Function LookupCode(ChargeCode As Range, vChargeCodes As Range) As Boolean

    Dim vRow As Long
    Dim vLookupCode As String

    LookupCode = False
    vRow = 2
    Do While LookupCode = False And ActiveSheet.Cells(vRow, 9) <> ""
        If ActiveSheet.Cells(vRow, 9) = ChargeCode Then
            If IsInArray(ActiveSheet.Cells(vRow, 8), Split(vChargeCodes, ",")) Then
                LookupCode = True
                Exit Do
            End If
        End If
        vRow = vRow + 1
    Loop

End Function

' variation of IsInArray from https://stackoverflow.com/questions/38267950/check-if-a-value-is-in-an-array-or-not-with-excel-vba
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    Dim vIndex As Long
    For vIndex = LBound(arr) To UBound(arr)
        If Trim(arr(vIndex)) = Trim(stringToBeFound) Then
            IsInArray = True
            Exit Function
        End If
    Next
    IsInArray = False
End Function

And then in the sheet...

enter image description here

TechnoDabbler
  • 1,245
  • 1
  • 6
  • 12
0

If your list of codes are ALWAYS formatted the same, i.e. first 4 characters are the code, followed by a comma and a space and then the next code you could use "if" and "mid" statements to grab each code

=VLOOKUP(MID(B2,1,4),$H$2:$I$4,2,FALSE) & IF(LEN(B2)>5,", " & VLOOKUP(MID(B2,7,4),$H$2:$I$4,2,FALSE),"")

Sorry I tend to use vlookup instead of index and match. That formula will work for two codes, repeat the 2nd part of the formula for as many codes as needed.

Goodluck!

Cooper
  • 68
  • 7