Xlookup in via with multiple criteria-CodePudding

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

CodePudding user response：

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

CodePudding user response：

I thank you again for the help. Your code works great. you are professional

but in real work
My data is too large.

Best regards sir.

[ issue ]

1. It takes a long time to calculate. 

2. I don't want the formula to be displayed in the cell. 
    (only value)  
3. I also want to calculate in rows and columns. 




 this code as below I'm was change some point.

 

 Dim ws As Worksheet

 Dim sh As Worksheet





Set ws = Worksheets("Sheet1")

'change point #1 add.viriable
Set sh = Worksheets("Sheet2")
Dim wsRow As Long
Dim shRow As Long

'change point #2 add count rows
wsRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
shRow = sh.Cells(Rows.Count, 1).End(xlUp).Row

Dim Ctr1, Ctr2, Result As Range
Set Ctr1 = ws.Range("A2:A" & wsRow)
Set Ctr2 = ws.Range("B2:B" & wsRow)
Set Result = ws.Range("C2:C" & wsRow) ' do you can change to dynamic column ?
                                       'Ex.colume = 5 --> Column  C to G

'  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


With sh
'change point #3 change for i =  to count rows
       For i = 2 To shRow ' about 100,000 rows
           p(0) = .Cells(i, 5).Address(0, 0) & "&" & .Cells(i, 6).Address(0, 0)
           .Cells(i, 7).Formula = "=XLOOKUP(" & Join(p, ",") & ")"
       Next

End With