Forum Discussion
Pulling together multiple data points after consolidating
I'm having an issue with consolidation in excel and looking for help. In this example I have four customers that have made multiple purchases each. I used the consolidate function to understand purchases per person. Easy enough. But, I also have other columns of data (company, email, etc) that I can't seem to pull into my consolidate function. So I'm left with name and total spend, but can't find a way to then pull in email and company. I'm guessing some sort of if/then combo could work but not sure how to craft the formula. In the below example there are only four people but in my actual use case there are nearly 2,000 so can't do it manually. What would I need to put into cell H2 so that if the name John appears in cell A2 then it should populate cell H2 with the data from C2?
1 Reply
mocojo I assume you used SUMIF in column G. For columns H and I you need to use a lookup function. Depending on your Excel version that may be VLOOKUP, INDEX together with MATCH or XLOOKUP.
Though, it may be wiser to create one table with the customer information (Name, Email and Company) and another table with all transactions (Name and Purchase). Then you don't have to duplicate all customer info for every transaction.
