Xlookup with multiple criteria in VBA

Question

I feel like this is something stupidly simple, but I've been Googling and experimenting for a while and seem to be coming up empty handed guess is I'm searching the wrong term/word. Anyway, let me explain.

for Example :

this code: Run -Time error '13' Type mismatch

Dim ws, sh As Worksheet

Set ws = Worksheets("Sheet1")

Set sh = Worksheets("Sheet2")
 
Dim Ctr1, Ctr2, Result As Range
 
Set Ctr1 = ws.Range("A2:A100")

Set Ctr2 = ws.Range("B2:B100")

Set Result = ws.Range("C2:C100")
 

With sh 
    .Cells(2, 7).Value = WorksheetFunction.XLookup( _
    .Cells(2, 5) & .Cells(2, 6), Ctr1 & Ctr2, Result, 0)
       
End With

 
End Sub

stackoverflow.com/questions/61594077/…

braX
– braX

2021-12-15 09:23:04 +00:00
Commented Dec 15, 2021 at 9:23 — braX
– braX, Commented Dec 15, 2021 at 9:23
stackoverflow.com/a/69084665/8422953

braX
– braX

2021-12-15 09:30:43 +00:00
Commented Dec 15, 2021 at 9:30 — braX
– braX, Commented Dec 15, 2021 at 9:30

CDP1802 · Accepted Answer · 2021-12-15 13:39:05Z

1

Create the XLOOKUP parameters from the range addresses.

Sub Macro1()

    Dim ws As Worksheet, sh As Worksheet
    Set ws = Worksheets("Sheet1")
    
    Dim Ctr1, Ctr2, Result As Range
    Set Ctr1 = ws.Range("A2:A100")
    Set Ctr2 = ws.Range("B2:B100")
    Set Result = ws.Range("C2:C100")
    
    '  XLOOKUP parameters
    Dim p(3) As String, i As Long, w As String
    w = "'" & ws.Name & "'!"
    p(1) = w & Ctr1.Address(0, 0) & "&" & _
           w & Ctr2.Address(0, 0)
    p(2) = w & Result.Address(0, 0)
    p(3) = 0
    
    Set sh = Worksheets("Sheet2")
    With sh
        For i = 2 To 2
            p(0) = .Cells(i, 5).Address(0, 0) & "&" & .Cells(i, 6).Address(0, 0)
            .Cells(i, 7).Formula = "=XLOOKUP(" & Join(p, ",") & ")"
        Next
    End With
    
End Sub

answered Dec 15, 2021 at 13:39

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Xlookup with multiple criteria in VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related