vlookup limitation
 

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

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.

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

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?

"Bob Phillips" wrote:

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

Hi Ankur,

Can I help you with:
http://home.planet.nl/~oost1250/Excel/multivlookup.xls

Kees Oosterveld.


"Ankur" wrote:

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?

"Bob Phillips" wrote:

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