Vlookups with multiple values for same ID

Hi all, are these possible? (Excel 2010)

I want to return every value for the same ID, to get a full list of all the values possible for this ID.

So far it is only returning the first value which it finds entered in the spreadsheet.

I have attached the workbook - it is a basic practice version for the full one I want to construct.

The original table is on the left and the Lookup table where I want to get the answer, is on the right.

Thanks!

Daniel

Take a look at this thread from earlier today:

http://www.excelforum.com/excel-gene...show-data.html

Hope this helps.

Pete

So, if I understand correctly, for Wine you want 1, 3, and 6 (perhaps in cells G2 through I2), for Commodities, 10, 40, and 60, and so forth? And, if correct, is there an upper limit to the number of values you want reported for a given ID?

Thanks to everyone for replying...I have been told by the employer not to use SUMIF or COUNTIF because they consider VLookup to be more efficient...so I need to do it this way as an example, in order to learn, so I can apply it to the real spreadsheet (which is much larger, here I just used a few days instead of the whole year)

Originally Posted by bentleybob

So, if I understand correctly, for Wine you want 1, 3, and 6 (perhaps in cells G2 through I2), for Commodities, 10, 40, and 60, and so forth? And, if correct, is there an upper limit to the number of values you want reported for a given ID?

Hi, yes this is exactly correct. No upper limit necessarry. I have no idea how to do this because if I copy and paste down, it only gives me those same values (Wine=1, Commodities=10, Real Estate=5).

Thanks...

Originally Posted by Haseeb A

Try this in G2 with CTRL+SHIFT+ENTER, rather than just ENTER

Please Login or Register  to view this content.

Then copy G2, then paste across & down.

Thanks for the effort man I appreciate it!

I tried this but I got 0! :-s

I changed it to include a larger table array, but still 0.

Anyway, I now understand how I should have phrased my question: "Can we use VLookups with non-unique identifiers".

Originally Posted by icestationzbra

you cannot use VLOOKUP for that purpose.

see if attached satisfies your requirement.

depending on whether you want your Summary laid out landscape or portrait, the formula is slightly different. both the versions are provided.

This is great, thank you so much!