Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions |