Thank you Pascal.. :)

is it also possible that we can change the lookup column according to the
value of the cell?

for eg., in the below statement

if the value of Range("C4") = "First" it should lookup in Range("J" & lngRow)

if the value of Range("C4") = "Sec" it should lookup in Range("M" & lngRow)

------------------------------------------

(Sheets("WFM Details").Range("J" & lngRow) = .Range("C4") Or
Len(.Range("C4").Value) = 0) And _