Using vlookup sort one column into multiple columns

Hi All,

This is my first post and I have limited excel knowledge so apologies if this question has been answered somewhere else already. I am just getting to grips with the excel terminology so searching is rather difficult when you don't know how to correctly describe your problem

I have attached an example of what I am trying to do on a much larger scale. I receive a download from a supplier of options/items purchased by customers separated into anonymised single row entries. I then need to convert this into a table so I can see all the customers who have purchased Item A, Item B, Item C, etc. in separate columns to later sort and filter them.

I can do this the long way by separating them out into a table of all entries of a specific item type and if vlookup in that table is true, a value is returned. This takes extra time to set up and process and I would rather have a forumla in each column that reads something along the lines of:

=IFERROR(IF(VLOOKUP(A2,Download,2,0)="A","A",""),"")
=IFERROR(IF(VLOOKUP(A2,Download,2,0)="B","B",""),"")
=IFERROR(IF(VLOOKUP(A2,Download,2,0)="C","C",""),"")
etc.

The problem with this is it stops after the first vlookup entry that relates to that customer rather than continuing to check all entries relative to that customer to see if the formula is true further down. How do I get it to continue this process or work around this limitation of vlookup functions.

Thanks in Advance,
TMAC

Hi all,

Thank you for the help.

By replacing the Red with "Item name" and the Green with "Abrieviated item name", I am able to process the information at the same time.

By adding the whole formula again in the Blue section of the formula I have been able to add e.g "Basic" or "Premium" as final item names in the same output column to determine levels of the same item.

=IF(SUMPRODUCT(--($A2&RIGHT(B$1)=INDEX(Download,,1)&INDEX(Download,,2))),RIGHT(B$1),"")

This has made my day so much quicker, I cannot thank you all enough!

TMAC

Hi Ali,

The solution you provided was another great solve but it requires an extra step compared to the formula Bo provided. Thanks for the help though as for a quick solution your method is perfect.

All the best,
TMAC.