• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup multiple values for items in a column

S

sameergaur

New Member
Hi There,


I am trying to perform a vlookup type of action on the items in a column, the only thing is that I am trying to lookup multiple values for each of the items from an array. Some items could return 3 values, some can 5, some can 10, etc.


I counted the number of values each item would return using countif and added empty rows below each item accordingly. Then I used the index formula to look up the multiple values, but I can only do it for one item at a time, I have to change the formula every time to lookup values for other items.


Is there a way I could do it for all items (100s of them) at one time like we do it in vlookup by copying the formula in subsequent cells?


Here is the sample sheet..


https://docs.google.com/file/d/0B5iClIFixWmgSWcwSUdRQWZoV00/edit?usp=sharing


Thanks

Sameer
 
I explain how to do such a thing in this article:

http://chandoo.org/wp/2011/11/18/formula-forensics-003/
 
Luke M,


Thanks for the reply. I read through your link and I could see that your formula returns multiple values. However I am still not sure how to run it for multiple items, say 50 or 100? For example in your sheet there are Fruits, Soft drinks, Juices, etc and they all have multiple types that we want to return just like the Vegetables.


Is there a way to do that without having to change the formula every time for Fruits, Soft Drinks, etc.?


Thanks

Sameer
 
You could copy the formula to other cells, and just have different criteria for each column. One way might be to have the criteria listed in E1 instead of D2, and then the formula would be:

=IF(COUNTIF(A:A,E$1) < ROWS($E$2:E2),"",INDEX(B:B,SMALL( IF($A$2:$A$10=E$1,ROW($A$2:$A$10)),ROW(A1))))

Copy this formula down and across as needed, with the different items you want listed across in row 1.


However, I'm not sure what the overall goal is. If you really want all the values for 50+ different things at once, why not just look at original data? Or, perhaps a PivotTable? With main items in first Row field and child items in secondary row field.
 
I understand. This is just a part of the data. I am trying to combine two different data sheets together. I have Items which have multiple values and then those multiple values have multiple values of their own. I am trying to compile all of it together for better visibility.


I will try the new formula and let you know how it goes.


Thanks

Sameer
 
You must log in or register to reply here.
Back
Top