CODEWITHSUNDEEP
excelvba
Vikestart
Vikestart

Reputation: 55

Excel VBA Lookup two values in same row based on values in three columns

I have a table that looks like this:

Table

Basically, "MatGroup" is a subtype of "StorLoc" which is a subtype of "Department".

I need to look for a match in those three columns, then return the values of two of the other columns to the right (those columns represent weeks).

I've highlighted an example in the picture above. There I'm searching for Department 1101, StorLoc 0001 and MatGroup 1225 in week 4 and 5, which should return the values 243 and 245, which can then be added together = 488.

For testing purpose, I have a working formula that does this in the same worksheet:

=SUMPRODUCT(XLOOKUP(K6&J8&K7;A2:A40&B2:B40&C2:C40;D2:E40))

However, I need a pure VBA solution for this as I'll be pulling this data into a completely separate workbook. I'm not able to use the same solution as in the formula above because apparantly VBA does not allow you to concat multiple criteria with &.

My current solution of using tons of CountIfs is not exactly satisfying and completely a lot more resource intensive than necessary (and messy!):

resultCount = Application.WorksheetFunction.SumIfs(dataWorkBook.Range("A" & departmentStartRow & ":BC" & departmentEndRow).Columns(weekNumber + 1), dataWorkBook.Range("B" & departmentStartRow & ":B" & departmentEndRow), StorLoc, dataWorkBook.Range("C" & departmentStartRow & ":C" & departmentEndRow), MatGroup)

Upvotes: 0

Views: 882

Answers (1)

FaneDuru
FaneDuru

Reputation: 42256

Please, try the next code. It uses an array and should be very fast even for big ranges, working in memory:

Sub LookupThreeConditions()
 Dim sh As Worksheet, sh1 As Worksheet, lastRow As Long, arr, i As Long
 Dim dep As String, stLoc As String, matGr As String, result As Long
 
 dep = "1101": stLoc = "0001": matGr = "1225"
 Set sh = ActiveSheet
 Set sh1 = sh.Next 'use here the sheet you need to copy in
 
 lastRow = sh.Range("A" & sh.rows.Count).End(xlUp).row
 arr = sh.Range("A2:F" & lastRow).value 'put the range in an array
 For i = 1 To UBound(arr)
    If arr(i, 1) & arr(i, 2) & arr(i, 3) = dep & stLoc & matGr Then
        result = arr(i, 5) + arr(i, 6): Exit For
    End If
 Next i
 MsgBox "Result is " & result
 sh1.Range("A1").value = result 'use here what range you need
 End Sub

Upvotes: 2

Related Questions