xlookup 2 criteria

[charly1]

Hi all
Does anyone have time to help me with the code below. I have attempted to write code with a double lookup criteria and it isn't working.
Thanks so much in advance!

Dim ActiveRow As Long


ActiveRow = ActiveCell.Row

Dim tblartist As Range
Dim tblname As Range
Dim tbleref As Range

Set tblartist = Worksheets("sheet1").ListObjects("Table1").ListColumns(14).DataBodyRange
Set tblname = Worksheets("sheet1").ListObjects("Table1").ListColumns(5).DataBodyRange
Set tbleref = Worksheets("sheet1").ListObjects("Table1").ListColumns(1).DataBodyRange

TextBox1.Value = WorksheetFunction.XLookup((ActiveRow, 5) & (ActiveRow, 7), tblartist & tblname, tbleref)

 

[Jeffrey Mahoney]

Xlookup won't return anything by looking in two columns for information. My suggestion is that you create a helper column that concatenates the values from those two columns.

Now as far as the reference (Activerow,5), I'm pretty sure you can't reference a cell like that. You may want to use Activesheet.cells(Activerow, 5).

Dim LookupTxt as string
Dim aSht as worksheet
Dim ActiveRow as long

ActiveRow = Activecell.Row
set aSht = Activesheet
LookupTxt = aSht.cells(ActiveRow, 5).value & aSht.cells(ActiveRow, 7).value

Then you can use LookupTxt in the XLookup formula and the new helper column

Jeff

 

[charly1]

Xlookup won't return anything by looking in two columns for information. My suggestion is that you create a helper column that concatenates the values from those two columns.

Now as far as the reference (Activerow,5), I'm pretty sure you can't reference a cell like that. You may want to use Activesheet.cells(Activerow, 5).

Dim LookupTxt as string
Dim aSht as worksheet
Dim ActiveRow as long

ActiveRow = Activecell.Row
set aSht = Activesheet
LookupTxt = aSht.cells(ActiveRow, 5).value & aSht.cells(ActiveRow, 7).value

Then you can use LookupTxt in the XLookup formula and the new helper column

Jeff

Thanks so much for your time.

I don't know that much about excel, although the below xlookup formula does work fine in my spreadsheet, and it does refer to 2 separate columns.

=XLOOKUP(E11&G11,Table1[[#All],[מחבר]]&Table1[[#All],[ספר]],Table1[[#All],[מספר]])

All I am trying to do is write the exact same formula in vba - exchanging the two lookup values for two ranges

 

[Jeffrey Mahoney]

So, I guess you can use multiple columns in Xlookup using the Ampersand between them. I tried several ways to get to work in VBA

 

[charly1]

So, I guess you can use multiple columns in Xlookup using the Ampersand between them. I tried several ways to get to work in VBA

Thanks for your time. I gave up trying to do it in vba and just went along with your idea of helper columns.

 

[Alex Blakenburg]

Apparently we are in good company and Bill Jelen has a similar issue.

Give this a try:

Sub BillJelensMethod()

    Dim ActiveRow As Long
    
    ActiveRow = ActiveCell.Row
    
    Dim tblartist As Range
    Dim tblname As Range
    Dim tbleref As Range
    
    Set tblartist = Worksheets("sheet1").ListObjects("Table1").ListColumns(14).DataBodyRange
    Set tblname = Worksheets("sheet1").ListObjects("Table1").ListColumns(5).DataBodyRange
    Set tbleref = Worksheets("sheet1").ListObjects("Table1").ListColumns(1).DataBodyRange
    
    Dim LookFor As Variant
    Dim LookIn As Variant
    Dim LookReturn As Variant
    
    LookFor = Cells(ActiveRow, 5).Value & Cells(ActiveRow, 7).Value
    LookIn = Evaluate("=" & tblartist.Address(External:=True) & "&" & tblname.Address(External:=True))
    
    ActiveCell = WorksheetFunction.XLookup(LookFor, LookIn, tbleref, "Not Found")

End Sub

Around the 5 min mark.