Find VLookup value using CountIF function

     

    This code is useful in the below mentioned scenarios:

  • Lookup value exists only once in data base
  • Lookup value doesn't exists even single time in database
  • Lookup value exists more than once(duplicates exists)
  •  

    Retrieve Lookup value using countif Function

     

     

    Sub FindTheLookupValueUsingCountIFFunction()
    'This tempalte is useful in the below mentioned scenarios:
    '(1) lookup value exists in Database only once
    '(2)Duplicates exists to Lookup Value in Database
    '(3) Lookup Value doesn't exists in Database


    'Define the workbook
    Dim WKB As Workbook
    Set WKB = ActiveWorkbook

    'Define the Lookup Sheet
    Dim LookupSH As Worksheet
    Set LookupSH = WKB.Sheets("LookUPValue")
    LookupSH.Range("B2:C10").ClearContents

    'Define DataBase Worksheet
    Dim DBSH As Worksheet
    Set DBSH = WKB.Sheets("DataBaseSheet")

    'Define the LastRow for Database worksheet
    Dim DBLastRow As Integer
    DBLastRow = DBSH.Range("A" & Rows.Count).End(xlUp).Row

    Dim R As Integer
    R = 2
    LookupSH.Activate
    Do Until LookupSH.Range("A" & R).Value = ""
    LookupSH.Range("A" & R).Activate
    LookupValue = LookupSH.Range("A" & R).Value
    HitNumber = WorksheetFunction.CountIf(LookupSH.Range("A2:A" & R), LookupValue)

    For i = 2 To DBLastRow
    With DBSH
    'Hit count of Lookup value in Database has to match with = Hit number
    If WorksheetFunction.CountIf(.Range("A2:A" & i), LookupValue) = HitNumber Then
    LookupSH.Range("B" & R).Value = .Range("B" & i).Value
    Exit For ' Exit the Loop if Hit count matched
    End If
    End With
    Next

    'Lookup value doesn't exists in Database Even Single time
    If HitNumber = 1 And LookupSH.Range("B" & R).Value = "" Then
    LookupSH.Range("B" & R).Value = "Data Doesn't exists Even Single time"
    End If

    'Data doesn't exists for Nth time
    If LookupSH.Range("B" & R).Value = "" Then
    LookupSH.Range("B" & R).Value = HitNumber & " Time Not Available in Data Range"
    End If
    'Place the Hit count in column C
    LookupSH.Range("C" & R).Value = HitNumber
    R = R + 1
    Loop
    End Sub

    Download the Workbook