Ankur,

you can do it with

=INDEX(B2:B10,SMALL(IF(A2:A10=C1,ROW(A2:A10)-1,""),ROW(A2:A10)-1))

where C1 holds the value to lookup, A2:A10 holds the lookup values, and
B2:B10 holds the values to list.

Then drag and copy this formula down for your anticipated maximum number of
hits, and with all cells still selected, hit F2, and then commit with
Ctrl-Shift-Enter as it is an array formula.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ankur" wrote in message
...
when there are common refrence appearing in different cells in same column
for which we are using lookup function then it picks only the first value
from the reference sheet, is there any solution for this?

Thanks for your help