vlookup limitation

A

Ankur

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
 
D

Dave O

VLOOKUP is doing its job: finding the first instance in a list, and
returning a value. Do you need to sum all the values associated with
your lookup item? If yes then try SUMIF.
 
B

Bob Phillips

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)
 
A

Ankur

Hi bob,
thanks for your help, seems to be very complex formula. One more thing is it
a substitution of Vlookup function. i.e. in all the cells i have to enter
this formula only?
 
Top