Using xLookUp in VBA to return range of values

I'm attempting to use Xlookup in VBA. I've used it before as a formula in Excel sheets, but I am stuck on getting it to work with returning a range of values in VBA.

For example, the below works fine as Xlookup is only returning a single value - the value in column B:

Range("E1") = Application.WorksheetFunction.XLookup(sValue, Range("A:A"), Range("B:B"), "Tag Not Found", 0, 1)

As soon as I increase the return range to more than one column, I get nothing returned:

Range("E1") = Application.WorksheetFunction.XLookup(sValue, Range("A:A"), Range("B:C"), "Tag Not Found", 0, 1)

Within Excel, this formula would simply put the first value in E1, then 'spill' the second value over into F1.

In the latter example, I see the returned type is variant(1 to 2). Is there a trick to make this behave like Excel, and simply spill over?

I guess I could put the returned value into a variable, then loop through it and extract each element - but I would like to know if there is a cleaner way to do this.

2 Answers

It's always a good idea to return the result of using a worksheet function in VBA to a variable.

The main reason for that would be to check for errors but another is when the result could return multiple values in an array

When an array is returned then you can use UBound to get the no of rows and columns and resize the destination range appropriately.

Dim Res As Variant Res = Application.XLookup(sValue, Range("A:A"), Range("B:C"), "Tag Not Found", 0, 1) If Not IsError(Res) Then If Not IsArray(Res) Then Range("E1").Value = Res End If Range("E1").Resize(UBound(Res,1), UBound(Res,2)).Value = Res Else MsgBox "XLOOKUP returned an error!", vbInformation End If

To make things spill dynamically you need a formula. One of XLOOKUP's tricks is that it can return a Range rather than just values. So try using this formula;

Range("e1").Formula2 = "=" & Application.WorksheetFunction.XLookup(2, Range("A:A"), Range("B:C")).Address