Is there a VBA equivalent of XLookup for arrays?

[JenniferMurphy]

Suppose I have a VBA array (n,2) something like this, but in a VBA variable, not a sheet range.

Is there something in VBA like XLookup in Excel that will do this, but with a VBA array?

Weighted Ratings.xlsm
	P	Q	R	S
5	A	9	C	4
6	B	7	E	1
7	C	4	A	9
8	D	2
9	E	1
Test

Cell Formulas
Range	Formula
S5:S7	S5	=XLOOKUP(R5,$P$5:$P$9,$Q$5:$Q$9)

I know I can do it in a loop. Since the table is small, that's probably OK. I just wondered if there is something better. Thanks

 

[Dan_W]

Im not currently at my computer, so haven't tested this, I would've thought you could just use `XLOOKUP` from VBA with `WorksheetFunction`. So:

Result = WorksheetFunction.Xlookup(....

And then add the usual parameters like you would when using it in the worksheet (I don't remember what they are off the top of my head).

 

[Alex Blakenburg]

This format will work.

Sub test_xlookup()

    Dim rng As Range
    Dim arr As Variant
    
    Set rng = Range("A1").CurrentRegion
    arr = rng.Value
    
    Dim vresult As Variant
    
    With WorksheetFunction
        vresult = .XLookup("x", .Index(arr, 0, 1), .Index(arr, 0, 2), "Not Found")
    End With
    
    Debug.Print vresult


End Sub

 

[JenniferMurphy]

Im not currently at my computer, so haven't tested this, I would've thought you could just use `XLOOKUP` from VBA with `WorksheetFunction`. So:

Result = WorksheetFunction.Xlookup(....

And then add the usual parameters like you would when using it in the worksheet (I don't remember what they are off the top of my head).

Ok. Suppose this array is named arrTest.

The XLookup syntax is

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

How do I specify the 2nd and 3rd parameters? For lookup_array I want column 1. For return_array I want column 2.

 

[JenniferMurphy]

This format will work.

With WorksheetFunction
vresult = .XLookup("x", .Index(arr, 0, 1), .Index(arr, 0, 2), "Not Found")
End With

End Sub
[/CODE]

It was the index function that I didn't think of. Thanks. That works perfectly.

 

[Alex Blakenburg]

You're welcome. Glad we could help.