lookup formula to return all result in table_array

[office 365 office 365]

Hi excel community,

what is the best lookup formula to return all result in table_array. 

This is my sample dataset

Customer name	Item sold	Date
Ali	Books	1/1/2010
Ali	Table	2/5/2010
Ali	Furniture	6/8/2010
James	Furniture	7/1/2010
James	Books	8/3/2010
James	Table	9/10/2010

I want to generate the report how much item sold by Customer name.

If i select customer name (Ali), i want all result for item sold and date for Ali will return as per below

Customer name	Ali
Item sold	Books	1/1/2010
	Table	2/5/2010
	Furniture	6/8/2010

[#2]

Hi,

Recently, I've replied to a similar question.

Please check it out here.

Regards

[#3]

Alternatively you can use a pivot table. Please check the attached file.

[#4]

This is a good solution!

But a problem that may occur when the same customer buys the same item again in a different day.

In this case, the PivotTable will show up only the most recent item, this is because you choose to summarize the values by max.

I think the most perfect solution is done by using Power Query as the below screenshot and the attached workbook.

Try to change the customer name, then right-click and refresh the orange table and see how it works.