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