How to use XLookup on VBA by searching 2 criteria/Condition

Dear EssperTan
It looks like you're trying to concatenate the values in cells G7 and G9 and use them as a combined lookup value in VBA. However, the syntax for concatenation is incorrect in your code.

Here's the corrected version of your VBA code:

Please Login or Register  to view this content.

I use Range("G7").Value & Range("G9").Value to concatenate the values in cells G7 and G9.
I use .Value to access the values of the ranges in MasterList.
I also include the "NotFound" argument at the end of the XLookup function, similar to your original Excel formula.
Make sure that the ranges you are working with contain valid data, and the concatenated value from G7 and G9 is found in the combined ranges A:A and B:B in MasterList. If the concatenated value is not found, the XLookup function will return the specified "NotFound" value.

Hi Sudbhavani,

Thanks for the reply!
I had tried using your code, but I'm receiving some mismatch error.

Let me give a rough idea of what I want to achieve too.
So basically, I'm trying to make an user interface whereby the quantity balance will be deducted base on the name & manufacturer. Hence that's why I need to use XLookup for better accuracy as it can find 2 value and return the balance value back.

Do have a look at my GUI & the code below for better understanding, thanks for all the help given!
Attachment 853111



<----------Here is my full list of VBA code-------------------->
Private Sub ComponentAvailbility_Click()

Dim Check As String
Dim Number As String

Check = Range("J7")
Number = Range("J8")
If Check = "Yes" Then
MsgBox "Item is available"
Else
MsgBox "Item is not available in the store"
End If

End Sub

Private Sub Execute_Click()

Dim CellValue As String
Dim ValueCheck As String
Dim ItemAvailable As String
Dim QtySubt As String
Dim NameCheck As String
Dim Value1 As Integer, Value2 As Integer, EnoughValue As Integer

ValueCheck = Range("J8") 'Checking for value keyed in (number or text)
ItemAvailable = Range("J7") 'Checking for item availability
NameCheck = Range("H6") 'Checking for username
If ValueCheck = "True" Then
If ItemAvailable = "Yes" Then
Value1 = Range("G11").Value
Value2 = Range("G10").Value

If NameCheck = "True" Then
EnoughValue = Value1 - Value2 'Ensure enough stock in store
If EnoughValue >= 0 Then

CellValue = Range("G12")


''============Vlookup code program section=====================================================================================

Dim longRow As Long
Dim longRow2 As Long

On Error Resume Next
longRow = Application.WorksheetFunction.Match(Worksheets("GUI").Range("G7"), Worksheets("MasterList").Range("A:A"), 0)
longRow2 = Application.WorksheetFunction.Match(Worksheets("GUI").Range("G9"), Worksheets("MasterList").Range("B:B"), 0)
If Err.Number <> 0 Then
MsgBox " Search failed"
Exit Sub
End If
On Error GoTo 0
Worksheets("MasterList").Range("A:A").Cells(longRow, 4) = Sheets("GUI").Range("G12")

''================================Checking XLoopup to replace Vlookup code program==============================================

'Range("G13").Value = Application.WorksheetFunction.XLookup(Range("G7"), Sheets("MasterList").Range("A:A"), Sheets("MasterList").Range("B:B"), Sheets("MasterList").Range("D:D")) 'working but single lookup

'Range("G14").Value = Application.WorksheetFunction.XLookup(Range("G7" & "G9"), Sheets("MasterList").Range("A:A") & Sheets("MasterList").Range("B:B"), Sheets("MasterList").Range("D:D"))

'Range("G14").Value = Application.WorksheetFunction.XLookup(Range("G7") & Range("G9"), Sheets("MasterList").Range("A:A") & Sheets("MasterList").Range("B:B"), Sheets("MasterList").Range("D:D"), "NotFound")
Range("G14").Value = Application.WorksheetFunction.XLookup(Range("G7").Value & Range("G9").Value, Sheets("MasterList").Range("A:A").Value & Sheets("MasterList").Range("B:B").Value, Sheets("MasterList").Range("D:D").Value, "NotFound")

'========================================================================================================

MsgBox "The remaining Qty is " & CellValue


Else

MsgBox "Insufficient Quantity in store"

End If
Else
MsgBox "Please enter a valid name!"
End If
Else
MsgBox "Please enter an valid component"
End If
Else
MsgBox "Please insert a numeric number!"
End If


End Sub
<----------------------------------------------------------->

It looks like there are a few issues in your code. I'll provide some corrections and explanations to help you understand the changes. I've modified the relevant part of your code where you use XLookup:

Please Login or Register  to view this content.

Explanation of changes:

I introduced a result variable to store the result of the XLookup function.
I removed the concatenation of Range("G7").Value & Range("G9").Value inside the XLookup function, as XLookup can handle multiple criteria without concatenation.
I added error handling to check if the XLookup was successful. If successful, it updates CellValue with the result; otherwise, it displays an error message.
Make sure to adapt this code to fit the structure of your workbook and the logic of your application.

I had implemented the code in, now I am able to compile the program but I'm unable to retrieve any value from it as shown in the attached picture.
and the value does not
May I know where did I put wrong?

Thank you for all the help given!

Attachment 853224 <- no value Attachment 853228 <- got value for checking
---------- Here is the updated code --------------

Private Sub Execute_Click()

Dim CellValue As String
Dim ValueCheck As String
Dim ItemAvailable As String
Dim QtySubt As String
Dim NameCheck As String
Dim Value1 As Integer, Value2 As Integer, EnoughValue As Integer

ValueCheck = Range("J8") 'Checking for value keyed in (number or text)
ItemAvailable = Range("J7") 'Checking for item availability
NameCheck = Range("H6") 'Checking for username
If ValueCheck = "True" Then
If ItemAvailable = "Yes" Then
Value1 = Range("G11").Value
Value2 = Range("G10").Value

If NameCheck = "True" Then
EnoughValue = Value1 - Value2 'Ensure enough stock in store
If EnoughValue >= 0 Then

CellValue = Range("G12")
MsgBox "The cellvalue Qty is " & CellValue 'Just to check the value (to be remove after everything works)

''============Vlookup code program section=====================================================================================

'Dim longRow As Long
'Dim longRow2 As Long
'
'On Error Resume Next
'longRow = Application.WorksheetFunction.Match(Worksheets("GUI").Range("G7"), Worksheets("MasterList").Range("A:A"), 0)
'longRow2 = Application.WorksheetFunction.Match(Worksheets("GUI").Range("G9"), Worksheets("MasterList").Range("B:B"), 0)
'If Err.Number <> 0 Then
'MsgBox " Search failed"
'Exit Sub
'End If
'On Error GoTo 0
'Worksheets("MasterList").Range("A:A").Cells(longRow, 4) = Sheets("GUI").Range("G12")

''============XLookup code program section=====================================================================================

Dim result As Variant

On Error Resume Next
result = Application.WorksheetFunction.XLookup(Range("G7").Value & Range("G9").Value, Sheets("MasterList").Range("A:A").Value & Sheets("MasterList").Range("B:B").Value, Sheets("MasterList").Range("D:D").Value, "NotFound")
On Error GoTo 0

If Not IsError(result) Then
' Value found, update the balance
CellValue = result
MsgBox "The remaining Qty is " & CellValue
Else
' Value not found
MsgBox "Search failed"
End If

''========================================================================================================



''================================Checking XLoopup to replace Vlookup code program==============================================

'Range("G13").Value = Application.WorksheetFunction.XLookup(Range("G7"), Sheets("MasterList").Range("A:A"), Sheets("MasterList").Range("B:B"), Sheets("MasterList").Range("D:D")) 'working but single lookup

'Range("G14").Value = Application.WorksheetFunction.XLookup(Range("G7" & "G9"), Sheets("MasterList").Range("A:A") & Sheets("MasterList").Range("B:B"), Sheets("MasterList").Range("D:D"))

'Range("G14").Value = Application.WorksheetFunction.XLookup(Range("G7") & Range("G9"), Sheets("MasterList").Range("A:A") & Sheets("MasterList").Range("B:B"), Sheets("MasterList").Range("D:D"), "NotFound")
'Range("G14").Value = Application.WorksheetFunction.XLookup(Range("G7").Value & Range("G9").Value, Sheets("MasterList").Range("A:A").Value & Sheets("MasterList").Range("B:B").Value, Sheets("MasterList").Range("D:D").Value, "NotFound")

'========================================================================================================

'MsgBox "The remaining Qty is " & CellValue


Else

MsgBox "Insufficient Quantity in store"

End If
Else
MsgBox "Please enter a valid name!"
End If
Else
MsgBox "Please enter an valid component"
End If
Else
MsgBox "Please insert a numeric number!"
End If


End Sub

Originally Posted by EssperTan

I had implemented the code in, now I am able to compile the program but I'm unable to retrieve any value from it as shown in the attached picture.
and the value does not
May I know where did I put wrong?

Thank you for all the help given!

Attachment 853224 <- no value Attachment 853228 <- got value for checking
---------- Here is the updated code --------------

Private Sub Execute_Click()

Dim CellValue As String
Dim ValueCheck As String
Dim ItemAvailable As String
Dim QtySubt As String
Dim NameCheck As String
Dim Value1 As Integer, Value2 As Integer, EnoughValue As Integer

ValueCheck = Range("J8") 'Checking for value keyed in (number or text)
ItemAvailable = Range("J7") 'Checking for item availability
NameCheck = Range("H6") 'Checking for username
If ValueCheck = "True" Then
If ItemAvailable = "Yes" Then
Value1 = Range("G11").Value
Value2 = Range("G10").Value

If NameCheck = "True" Then
EnoughValue = Value1 - Value2 'Ensure enough stock in store
If EnoughValue >= 0 Then

CellValue = Range("G12")
MsgBox "The cellvalue Qty is " & CellValue 'Just to check the value (to be remove after everything works)

''============Vlookup code program section=====================================================================================

'Dim longRow As Long
'Dim longRow2 As Long
'
'On Error Resume Next
'longRow = Application.WorksheetFunction.Match(Worksheets("GUI").Range("G7"), Worksheets("MasterList").Range("A:A"), 0)
'longRow2 = Application.WorksheetFunction.Match(Worksheets("GUI").Range("G9"), Worksheets("MasterList").Range("B:B"), 0)
'If Err.Number <> 0 Then
'MsgBox " Search failed"
'Exit Sub
'End If
'On Error GoTo 0
'Worksheets("MasterList").Range("A:A").Cells(longRow, 4) = Sheets("GUI").Range("G12")

''============XLookup code program section=====================================================================================

Dim result As Variant

On Error Resume Next
result = Application.WorksheetFunction.XLookup(Range("G7").Value & Range("G9").Value, Sheets("MasterList").Range("A:A").Value & Sheets("MasterList").Range("B:B").Value, Sheets("MasterList").Range("D:D").Value, "NotFound")
On Error GoTo 0

If Not IsError(result) Then
' Value found, update the balance
CellValue = result
MsgBox "The remaining Qty is " & CellValue
Else
' Value not found
MsgBox "Search failed"
End If

''========================================================================================================



''================================Checking XLoopup to replace Vlookup code program==============================================

'Range("G13").Value = Application.WorksheetFunction.XLookup(Range("G7"), Sheets("MasterList").Range("A:A"), Sheets("MasterList").Range("B:B"), Sheets("MasterList").Range("D:D")) 'working but single lookup

'Range("G14").Value = Application.WorksheetFunction.XLookup(Range("G7" & "G9"), Sheets("MasterList").Range("A:A") & Sheets("MasterList").Range("B:B"), Sheets("MasterList").Range("D:D"))

'Range("G14").Value = Application.WorksheetFunction.XLookup(Range("G7") & Range("G9"), Sheets("MasterList").Range("A:A") & Sheets("MasterList").Range("B:B"), Sheets("MasterList").Range("D:D"), "NotFound")
'Range("G14").Value = Application.WorksheetFunction.XLookup(Range("G7").Value & Range("G9").Value, Sheets("MasterList").Range("A:A").Value & Sheets("MasterList").Range("B:B").Value, Sheets("MasterList").Range("D:D").Value, "NotFound")

'========================================================================================================

'MsgBox "The remaining Qty is " & CellValue


Else

MsgBox "Insufficient Quantity in store"

End If
Else
MsgBox "Please enter a valid name!"
End If
Else
MsgBox "Please enter an valid component"
End If
Else
MsgBox "Please insert a numeric number!"
End If


End Sub

what error message your getting i not able open your attachment so please share the error messaged. so i able to correct the code

Hi, when I run the code. I'm getting a blank value in return. Attachment 853242

Dear EssperTan

i not able to open the attached i will explain the process to attached file: GoAdvanend tab and select the manage tab and attached the file and submits the post

Ok, here you go. Are you able to see it now?
GUI2.PNG

Here is the excel file too if you need it. I think it will be better too so you can see the whole process.

Dear EssperTan

please check the attachment i thank now code is ruining successfully is there any issue let me no what the issue your getting

Please Login or Register  to view this content.

You can never evaluate Runtime error by IsError function, but Err.Number.

Try change that block to something like

Please Login or Register  to view this content.

Thanks all for the help and knowledge given! the issues is finally resolved!

dear EssperTa
kindly mark add reputation and mark as solved