OzGrid

5 Criteria VLookup in Excel

< Back to Search results

 Category: [Excel]  Demo Available 

 

5 Criteria VLOOKUP In Excel

 

This custom function  can use the 1st 5 Columns of a table as matching criteria and return the nth column row to the right of the matching row range.

Function Five_Con_Vlookup(Table_Range As Range, Return_Col As Long, Col1_Fnd, Col2_Fnd, Col3_Fnd, Col4_Fnd, Col5_Fnd)

''EXCEL 2003 OR ABOVE

''WRITTEN BY OZGRID.COM

Dim rCheck As Range, bFound As Boolean, lLoop As Long

    

    On Error Resume Next

    Set rCheck = Table_Range.Columns(1).Cells(1, 1)

    With WorksheetFunction

        For lLoop = 1 To .CountIf(Table_Range.Columns(1), Col1_Fnd)

           Set rCheck = Table_Range.Columns(1).Find(Col1_Fnd, rCheck, xlValues, xlWhole, xlNext, xlRows, False)

           If UCase(rCheck(1, 2)) = UCase(Col2_Fnd) And _

                UCase(rCheck(1, 3)) = UCase(Col3_Fnd) And _

                UCase(rCheck(1, 4)) = UCase(Col4_Fnd) And _

                UCase(rCheck(1, 5)) = UCase(Col5_Fnd) Then

                bFound = True

                Exit For

            End If

        Next lLoop

    End With



    If bFound = True Then

        Five_Con_Vlookup = rCheck(1, Return_Col)

    Else

        Five_Con_Vlookup = "#N/A"

    End If

End Function

OR

Function Five_Con_Vlookup(Table_Range As Range, Return_Col As Long, Col1_Fnd, Col2_Fnd, Col3_Fnd, Col4_Fnd, Col5_Fnd)

''PRE EXCEL 2003

''WRITTEN BY OZGRID.COM

Dim rCheck As Range, bFound As Boolean, lLoop As Long, lRow As Long

    

    On Error Resume Next

    With WorksheetFunction

        For lLoop = 1 To .CountIf(Table_Range.Columns(1), Col1_Fnd)

           lRow = .Match(Col1_Fnd, Table_Range.Columns(1).Range("A" & lRow + 1 & ":A65536"), 0) + lRow

           Set rCheck = Table_Range.Columns(1).Range("A" & lRow)

           If UCase(rCheck(1, 2)) = UCase(Col2_Fnd) And _

                UCase(rCheck(1, 3)) = UCase(Col3_Fnd) And _

                UCase(rCheck(1, 4)) = UCase(Col4_Fnd) And _

                UCase(rCheck(1, 5)) = UCase(Col5_Fnd) Then

                bFound = True

                Exit For

            End If

        Next lLoop

    End With



    If bFound = True Then

        Five_Con_Vlookup = rCheck(1, Return_Col)

    Else

        Five_Con_Vlookup = "#N/A"

    End If

End Function

Use like;
=Five_Con_Vlookup($A$1:$H$20,6,"Apr",4,"Thu","Larry",55)
in any cell other than $A$1:$H$20

Where $A$1:$H$20 is the table range

6 is the nth column to return the matching row from.

"Apr",4,"Thu","Larry" and 55 are the 5 conditions to match is the 1st 5 Columns

See also:

2 Criteria VLookup for Excel
3 Criteria VLookup for Excel
4 Criteria VLookup for Excel

 

Free Training Course: Lesson 1 - Excel Fundamentals

 

See also: Index to Excel VBA Code; Index to Excel Freebies; Lesson 1 - Excel Fundamentals; Index to how to… providing a range of solutions

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

 


Gallery



stars (0 Reviews)