Double Vlookup

[bromy2004]

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

=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

 

[Sandeep Warrier]

Hi,

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

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
	A	B	C	D	E	F	G
1	Rng1	Rng2	RetRng		Val1	Val2	Result
2	A	G	M		E	K	Q
3	B	H	N
4	C	I	O
5	D	J	P
6	E	K	Q
7	F	L	R
8
Sheet1

Cell Formulas
Range	Formula
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">

 

[bromy2004]

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

 

[uarcher]

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?

 

[bromy2004]

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?

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
	A	B	C	D
1	Val1	Val2	CombVal	ReturnVal
2	AAA	CCC	AAACCC	1
3	AAA	DDD	AAADDD	2
4	AAA	EEE	AAAEEE	3
5	BBB	FFF	BBBFFF	4
6	BBB	GGG	BBBGGG	5
7	BBB	HHH	BBBHHH	6
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.

 

[Sandeep Warrier]

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

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

=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.

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.

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

     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.

 

[Krishnakumar]

Another way..

In G2,

=LOOKUP(2,1/((A2:A7=E2)*(B2:B7=F2)),C2:C7)

source: Sandeep's post

HTH