Using a worksheet function where you can use a VBA function is inefficient.

RowCount = 2
Person = Range("A" & RowCount)
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
Set LookupRange = Range(Range("B" & RowCount), Cells(2, LastCol))
Set c = Rows(RowCount).Find(what:=Person, _
LookIn:=xlValues, lookat:=xlWhole)
MsgBox (Person & " found at " & c.Address)


Try this

set c =

"Jacob Skaria" wrote:

Try this

Application.HLookup(Range("A2"),Range("B2:F6"),2)

OR

Application.Index(Range("B2:F6"),2,Application.Mat ch(Range("A2"),Range("B2:F2"),0))

If this post helps click Yes
---------------
Jacob Skaria


"Kashyap" wrote:

ColA ColB ColC ColD ColE ColF
Calvin Amy Andy Calvin Danie Greg
ABC GEF LMO GEF LMO
GEF LMO IJK LMO
LMO IJK IJK
IJK


In the above table, desiredoutput in ColA below Calvin

LMO
IJK

formula used
=HLOOKUP(A2,B2:F6,2)
=HLOOKUP(A2,B2:F6,3)