VBA xLookup on multiple columns

[darkybot]

Hi all, I'm new in the world of xlookup and macros. I had to redo my recorded macros because .select is a bad habit to get used to, so I redid my entire macros, and now it works really well when I call it from a button.
Anyway, my macro is this:

Sub SDS1()
'
' SDS Macro
'

Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")
       With ws.Range("U2:U" & ws.Range("D" & Rows.Count).End(xlUp).Row)
        .Formula = "=XLOOKUP(D2,'SDS003b'!I:I,'SDS003b'!U:U,"""")"
        .Value = .Value
        End With
End Sub

Basically I have 2 sheets, SDS003b is where the info will be search and Data is where the result will show up.
My question is, instead of doing another macros for column SDS003bV:V which is the one following the previous macro already mentioned, is there a way to add or spill the Xlookup to multiple columns?
I tried doing something like which it filed just column U with info but all the rest filled with zeros:

Sub SDS1()
'
' SDS Macro
'

Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")
       With ws.Range("U2:Z" & ws.Range("D" & Rows.Count).End(xlUp).Row)
        .Formula = "=XLOOKUP(D2,'SDS003b'!I:I,'SDS003b'!U:Z,"""")"
        .Value = .Value
        End With
End Sub

Hope it make sense what Im asking.

 

[jkpieterse]

You should only write the spilling formula into column U, so change this:
"U2:Z" &
back to
"U2:U" &

 

[darkybot]

You should only write the spilling formula into column U, so change this:
"U2:Z" &
back to
"U2:U" &

Thank Jan for the answer, tried but the xlookup only worked on column U without spilling to Z.
I tried this:
With ws.Range("U2:U" & ws.Range("D" & Rows.Count).End(xlUp).Row)
.Formula = "=XLOOKUP(D2,'SDS003b'!I:I,'SDS003b'!U:Z,"""")"
and this:
With ws.Range("U2:Z" & ws.Range("D" & Rows.Count).End(xlUp).Row)
.Formula = "=XLOOKUP(D2,'SDS003b'!I:I,'SDS003b'!U:Z,"""")"

 

[Alex Blakenburg]

There are 2 issues.
• to get it to spill you need to use Formula2 (not formula)
• .Value = . Value is only copying values for Column U which is where the spill formula is and in doing so you only end up with the column U values from the spill array

Give this a try:

Sub SDS1_Mod()

Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")
    With ws.Range("U2:U" & ws.Range("D" & Rows.Count).End(xlUp).Row)
        .Formula2 = "=XLOOKUP(D2,'SDS003b'!I:I,'SDS003b'!U:Z,"""")"       
    End With
    
    With ws.Range("U2#:U" & ws.Range("D" & Rows.Count).End(xlUp).Row & "#")
        .Value = .Value
    End With
End Sub

Note: A filter alternative would have been:

        .Formula2 = "=Filter('SDS003b'!U:Z,'SDS003b'!I:I = D2, """")"

 

[darkybot]

There are 2 issues.
• to get it to spill you need to use Formula2 (not formula)
• .Value = . Value is only copying values for Column U which is where the spill formula is and in doing so you only end up with the column U values from the spill array

Give this a try:

Sub SDS1_Mod()

Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")
    With ws.Range("U2:U" & ws.Range("D" & Rows.Count).End(xlUp).Row)
        .Formula2 = "=XLOOKUP(D2,'SDS003b'!I:I,'SDS003b'!U:Z,"""")"      
    End With
   
    With ws.Range("U2#:U" & ws.Range("D" & Rows.Count).End(xlUp).Row & "#")
        .Value = .Value
    End With
End Sub

Note: A filter alternative would have been:

        .Formula2 = "=Filter('SDS003b'!U:Z,'SDS003b'!I:I = D2, """")"

That works perfectly fine, now I know something else thank you Alex!

 

[Alex Blakenburg]

You're welcome. Glad we could help.

 

[darkybot]

You're welcome. Glad we could help.

Follow up: What if, let say it jumps a column so instead of ".Formula2 = "=XLOOKUP(D2,'SDS003b'!I:I,'SDS003b'!U:Z,"""")" goes U,V to Y,Z, skipping column X. How to do that?