PDA

View Full Version : Solved: multiple column vlookup



hardlife
09-15-2009, 02:33 AM
Please does somebody can try this, because it does not works for me,
it could be usefull formula, it is hard job for me :banghead: to make it working.

http://www.automateexcel.com/media/Multiple%20Vlookup.xlsm

http://www.automateexcel.com/2008/10/04/vba-udf-to-perform-a-3-parameter-vlookup/


Function ThreeParameterVlookup(Data_Range As Range, Col As Integer, Parameter1 As Variant, Parameter2 As Variant, Parameter3 As Variant) As Variant
'Declare Variables
Dim Cell
Dim Current_Row As Integer
Dim No_Of_Rows_in_Range As Integer
Dim No_of_Cols_in_Range As Integer
Dim Matching_Row As Integer

'set answer to N/A by default
ThreeParameterVlookup = CVErr(xlErrNA)
Matching_Row = 0
Current_Row = 1

No_Of_Rows_in_Range = Data_Range.Rows.Count
No_of_Cols_in_Range = Data_Range.Columns.Count

'Check if Col is greater than number of columns in range

If (Col > No_of_Cols_in_Range) Then
ThreeParameterVlookup = CVErr(xlErrRef)
End If
If (Col <= No_of_Cols_in_Range) Then
Do
If ((Data_Range.Cells(Current_Row, 1).Value = Parameter1) And _
(Data_Range.Cells(Current_Row, 2).Value = Parameter2) And _
(Data_Range.Cells(Current_Row, 3).Value = Parameter3)) Then
Matching_Row = Current_Row

End If
Current_Row = Current_Row + 1
Loop Until ((Current_Row = No_Of_Rows_in_Range) Or (Matching_Row <> 0))

If Matching_Row <> 0 Then
ThreeParameterVlookup = Data_Range.Cells(Matching_Row, Col)
End If

End If

End Function


happy and sunny day to everybody.

Bob Phillips
09-15-2009, 04:25 AM
Your code can be tweaked like so



Function ThreeParameterVlookup( _
Data_Range As Range, _
Col As Integer, _
Parameter1 As Variant, _
Parameter2 As Variant, _
Parameter3 As Variant) As Variant
Dim Cell
Dim Current_Row As Integer
Dim No_Of_Rows_in_Range As Integer
Dim No_of_Cols_in_Range As Integer
Dim Matching_Row As Integer

'set answer to N/A by default
ThreeParameterVlookup = CVErr(xlErrNA)
Matching_Row = 0
Current_Row = 1

No_Of_Rows_in_Range = Data_Range.Rows.Count
No_of_Cols_in_Range = Data_Range.Columns.Count + Data_Range.Column

'Check if Col is greater than number of columns in range
If (Col > No_of_Cols_in_Range) Then
ThreeParameterVlookup = CVErr(xlErrRef)
End If

If (Col <= No_of_Cols_in_Range) Then

Do

If ((Data_Range.Cells(Current_Row, 1).Value = Parameter1) And _
(Data_Range.Cells(Current_Row, 2).Value = Parameter2) And _
(Data_Range.Cells(Current_Row, 3).Value = Parameter3)) Then

Matching_Row = Current_Row
End If
Current_Row = Current_Row + 1

Loop Until ((Current_Row = No_Of_Rows_in_Range) Or (Matching_Row <> 0))

If Matching_Row <> 0 Then
ThreeParameterVlookup = Data_Range.Cells(Matching_Row, Col - Data_Range.Column)
End If
End If

End Function


but it is a very cumbersome approach, and it can be done with an array formula

=INDEX(J11:O18,MATCH(1,(J11:J18="Peter")*(K11:K18="Johnson")*(L11:L18=6),0),6)

hardlife
09-15-2009, 06:31 AM
xld, Many thanks for code tweaking because this was hard example :banghead:
:bow: also thank You a lot for formula that is better solution.

xld me is Wishing You HAPPY AND SUNNY DAY,

With Best Regards
Pavel Humenuk