Double Vlookup

B

bromy2004

Board Regular
Joined
Feb 8, 2008
Messages
63
Hi everyone,

I use a combination of Index and match to do a Vlookup on 2 criteria.

Return_Range = the range the Values are in
Find1 = the First Criteria
Find2 = The Second Criteria
Range1 = where the first Criteria is
Range2 = where the second Criteria is
with CTRL+SHIFT+ENTER
Code: 
=INDEX(Return_Range,MATCH(Find1,IF(Range2=Find2,Range1),0))

however it gets very tedious to enter this in several times a day.
i'm hoping to do something similar in Excel VBA as a UDF.
but i'm not sure how to write UDF's

i was thinking along the lines of
Dbl_Vlook(Return_Range,Find1,Find2,Range1,Range2)

Can anyone help?

Thanks
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
Hi,

How about this. The syntax would be =dbl_Vlook(Value1,Value1,Range1,Range2,ReturnRange)

Code: 
Function dbl_Vlook(Val1 As Variant, Val2 As Variant, Rng1 As Range, Rng2 As Range, RetRng As Range) As Variant

    Dim RetCol As Long
    Dim Val1Rng As Range
    Dim Val2Rng As Range
    Dim Val1Row As Long
    
    RetCol = RetRng.Column
    
    dbl_Vlook = "Value Not Found"
    
    With Rng2
    
        Set Val2Rng = .Find(What:=Val2, After:=.Cells(.Rows.Count, 1), LookIn:=xlValues, LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        If Not Val2Rng Is Nothing Then
        
            With Rng1
        
                Set Val1Rng = .Find(What:=Val1, After:=.Cells(.Rows.Count, 1), LookIn:=xlValues, LookAt:=xlWhole, _
                                    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                
                Val1Row = Val1Rng.Row
                
                dbl_Vlook = Cells(Val1Row, RetCol).Value
            
            End With
            
        End If
            
    End With

End Function
Excel Workbook
ABCDEFG
1Rng1Rng2RetRngVal1Val2Result
2AGMEKQ
3BHN
4CIO
5DJP
6EKQ
7FLR
8
Sheet1
Cell Formulas
RangeFormula
G2=dbl_Vlook(E2,F2,A2:A7,B2:B7,C2:C7)
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
 
Upvote 0
Hi sandeep.warrier,

Thank you.
that works perfectly.

could you please explain how that works.
i haven't seen or used "Find" in VBA before.

-Bromy
 
Upvote 0
Is there any reason you cannot concatenate Column A & Column B and do the same with value 1 and value 2 and use a regular Vlookup?
 
Last edited:
Upvote 0
uarcher said:
Is there any reason you cannot concatenate Column A & Column B and do the same with value 1 and value 2 and use a regular Vlookup?
Click to expand...

Yes. Because the Vlookup Function requires the Concatenated Range and the values to return be in that order on the spreadsheet
i.e.
Excel Workbook
ABCD
1Val1Val2CombValReturnVal
2AAACCCAAACCC1
3AAADDDAAADDD2
4AAAEEEAAAEEE3
5BBBFFFBBBFFF4
6BBBGGGBBBGGG5
7BBBHHHBBBHHH6
Sheet1


Where as the Index\Match Formula (And "sandeep.warrier" UDF) will work in any order as long as the ranges are the same size. and across sheets.
 
Upvote 0
bromy2004 said:
Hi sandeep.warrier,

Thank you.
that works perfectly.

could you please explain how that works.
i haven't seen or used "Find" in VBA before.

-Bromy
Click to expand...

Hi Bromy,

Glad to hear that it works fine.

The "Find" statement does exactly what it does in regular excel. It finds a certain value.

Your original formula said

Code: 
=INDEX(Return_Range,MATCH(Find1,IF(Range2=Find2,Range1),0))
I interpreted it to check if value 2 is present in range 2 and if it is then do a normal index/match on value 1 in range 1.

My UDF does exactly the same thing.

Code: 
With Rng2
       Set Val2Rng = .Find(What:=Val2, After:=.Cells(.Rows.Count, 1), LookIn:=xlValues, LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
will look for value 2 in Range 2.

In the find statement, "What" represents the value that you need to look for. "After" is the cell after which search will begin. .Rows.Count is the max row and hence the cell after that means looping back to cell 1. "Lookin=xlvalue" means that you're looking for a value so if the cell contains a formula, it'll look for the value returned by the formula and not look at the formula itself (I'm not very sure about this explanation though)... "LookAt" is whether you want to look at the entire cell content (which is xlwhole) or part of the content (which is xlPart). SearchOrder is you can search in columns or rows. Search direction is it'll look in consecutive cells. Match case is false which means it wont be case sensitive. you can change it to true if you want case sensitive.

Code: 
If Not Val2Rng Is Nothing Then
        
            With Rng1
        
                Set Val1Rng = .Find(What:=Val1, After:=.Cells(.Rows.Count, 1), LookIn:=xlValues, LookAt:=xlWhole, _
                                    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Value 2 is present, this code will look for value 1 in range 1

Code: 
     Val1Row = Val1Rng.Row

     RetCol = RetRng.Column
RetCol is the column number for the Return Range and Val1Row is the row number of the row in which value 1 is found.

Cells(Row,Column) will give the relevant cell. For example Cells(1,1) is cell A1.

Hence Cells(Val1Row,RetCol) will point to the cell containing the required return value and the .Value returns the value present in that cell.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
494
Peter_SSs
Peter_SSs
D
  • Question Question
Lookup value based on two column criteria
Replies
3
Views
424
Cubist
Cubist
A
  • Question Question
Vlookup Help
Replies
1
Views
379
duggie33
D
U
  • Question Question
Show date greater than today on schedule
Replies
5
Views
387
ulak
U
R
  • Question Question
VLOOKUP or MATCH not working ?why?
Replies
3
Views
1K
awoohaw
awoohaw

Forum statistics

Threads
1,223,060
Messages
6,169,861
Members
452,286
Latest member
noclue2000

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top