Multiple Criteria VlookUp VBA

Hi,

I have the following columns in Sheet1 A, B, C and I have same columns in Sheet2. Sheet2 is containing All year data and Sheet 1 is containing current months data.

I need a VBA which will be doing a multiple criteria based vlookup to pull up the value of Col C if SheetA!ColA = Sheet2!ColB And SheetA!ColB = Sheet2!ColA Then Pull the matching value from Sheet2!ColC To Sheet1!ColC

Please help.
LVL 9
suvmitraAsked:
Who is Participating?
 
krishnakrkcConnect With a Mentor Commented:
Hi,

Try


Kris
Function MULTILOOKUP(ByRef Rng1 As Range, ByVal Crit1, _
                    ByRef Rng2 As Range, ByVal Crit2, ByRef Rng3 As Range) As Variant
Dim a As String, b As String, c As String, Fmla

a = Rng1.Address(, , , 1): a = Mid$(a, InStr(1, a, "]") + 1)
b = Rng2.Address(, , , 1): b = Mid$(b, InStr(1, b, "]") + 1)
c = Rng3.Address(, , , 1): c = Mid$(c, InStr(1, c, "]") + 1)

If Not IsNumeric(Crit1) Then Crit1 = """" & Crit1 & """"
If Not IsNumeric(Crit2) Then Crit2 = """" & Crit2 & """"
Fmla = "LOOKUP(2,1/((" & a & "=" & Crit1 & ")*(" & b & "=" & Crit2 & "))," & c & ")"

MULTILOOKUP = Evaluate(Fmla)

End Function
Sub kTest()
Dim ws1 As Worksheet, ws2 As Worksheet, Sht1ColA As Range, Sht1ColB As Range
Dim Sht2ColA As Range, Sht2ColB As Range, Sht2ColC As Range, Sht1ColC As Range
Dim i As Long, r As Long, c, a, b, x

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
With ws1
    r = .Range("a" & .Rows.Count).End(xlUp).Row
    Set Sht1ColA = .Range("a2:a" & r)
    Set Sht1ColB = .Range("b2:b" & r)
    Set Sht1ColC = .Range("c2:c" & r)
    a = Sht1ColA
    b = Sht1ColB
    c = Sht1ColC
End With
With ws2
    r = .Range("a" & .Rows.Count).End(xlUp).Row
    Set Sht2ColA = .Range("a2:a" & r)
    Set Sht2ColB = .Range("b2:b" & r)
    Set Sht2ColC = .Range("c2:c" & r)
End With

For i = 1 To UBound(a, 1)
    x = MULTILOOKUP(Sht2ColC, a(i, 1), Sht2ColA, b(i, 1), Sht2ColB)
    If Not IsError(x) Then c(i, 1) = x
Next
Sht1ColC.Value = c
End Sub

Open in new window

0
 
ExcelGuideConsultantCommented:
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
You talk in terms of VBA so I presume you are processing row by row.  It is as suggested by Psychotec efficient to use sheet formulae.

Assuming you want to end up with data rather than formulae then we can apply ther formula via VBA and then switch the results to values.

Let us know the requirement with this in mind.

Chris
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
suvmitraAuthor Commented:
This is a part of an automation project. So yes I am alright with formula but then i need to apply this through VBA.
0
 
Saurabh Singh TeotiaCommented:
Suvmitra,
Can you please post your sample file please.
Saurabh...
0
 
suvmitraAuthor Commented:
As attached. Also as you can see I am trying to match Balnce O/S = Dispute which are containing currency values And most of the case these two column values does not match with each other for some fractional value. eg 28.04 = 28.03.
Therefore I want only to consider one decimal for matching more accurately.
eg. 28.0 = 28.0
 

EE.xls
0
 
Leo TorresSQL DeveloperCommented:
I am still not understanding how you want your final result to be can you repost your file with your expected in sheet3 ...
0
 
Leo TorresSQL DeveloperCommented:
I am assuming you meant this if not let me know
EE-done.xlsx
0
 
Leo TorresSQL DeveloperCommented:
FYI if you copy and past formula to another you must confirm this array formula

Confirmed with CTRL + SHIFT + ENTER

if you just copy and paste and hit enter it wont work
0
 
suvmitraAuthor Commented:
Thank you all I will get back to you soon.
0
 
Leo TorresConnect With a Mentor SQL DeveloperCommented:
Sorry I just saw realized last thread about rounding to nearest tenth...

Same as before Confirmed with CTRL + SHIFT + ENTER

Added an xls copy incase your still on 2003
EE-done.xlsx
EE-done.xls
0
 
suvmitraAuthor Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.