Find VLookup value using CountIF function
Retrieve Lookup value using countif Function
Sub FindTheLookupValueUsingCountIFFunction()
'(2)Duplicates exists to Lookup Value in Database
'(3) Lookup Value doesn't exists in Database
Dim WKB As Workbook
Set WKB = ActiveWorkbook
Dim LookupSH As Worksheet
Set LookupSH = WKB.Sheets("LookUPValue")
LookupSH.Range("B2:C10").ClearContents
Dim DBSH As Worksheet
Set DBSH = WKB.Sheets("DataBaseSheet")
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
If HitNumber = 1 And LookupSH.Range("B" & R).Value = "" Then
LookupSH.Range("B" & R).Value = "Data Doesn't exists Even Single time"
End If
If LookupSH.Range("B" & R).Value = "" Then
LookupSH.Range("B" & R).Value = HitNumber & " Time Not Available in Data Range"
End If
LookupSH.Range("C" & R).Value = HitNumber
R = R + 1
Loop
End Sub