Solved

# Multiple Criteria VlookUp VBA

Posted on 2010-01-07
1,098 Views
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

0
Question by:suvmitra

LVL 17

Expert Comment

0

LVL 59

Expert Comment

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

LVL 9

Author Comment

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

LVL 59

Expert Comment

Suvmitra,
Saurabh...
0

LVL 9

Author Comment

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

LVL 18

Accepted Solution

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
``````
0

LVL 8

Expert Comment

I am still not understanding how you want your final result to be can you repost your file with your expected in sheet3 ...
0

LVL 8

Expert Comment

I am assuming you meant this if not let me know
EE-done.xlsx
0

LVL 8

Expert Comment

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

LVL 9

Author Comment

Thank you all I will get back to you soon.
0

LVL 8

Assisted Solution

Sorry I just saw realized last thread about rounding to nearest tenth...

Same as before Confirmed with CTRL + SHIFT + ENTER

EE-done.xlsx
EE-done.xls
0

LVL 9

Author Closing Comment

Thanks.
0

## Featured Post

### Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.