Return a value based on two criterias

[Sammi8796]

Hi. I'm hoping you guys can help me get this answer. I'm sure its fairly simple for you guys. I am trying to gather info on a customer who ordered something last. I have all the data dumped on a "Data" tab and there may be multiple rows of the same item number and the same customer name. It looks like this:
Data Tab:

On different "Lookup" tab in the workbook, I want my lookup cells. In G6 on my lookup tab, I want to return the customer of the latest purchase date for that item by searching the item in column A and the latest date in column C.

I want to return the average sale price for each item (Column A) by looking at the data tab column I (Formula entered on the "Lookup" Tab in cell D6)

I also want to return the total sales for each item (Column A) by looking at column I (Formula entered on the "Lookup" tab in cell E6)

My Lookup Tab looks like this:

I really don't want to spend a ton of time doing filter sorts and manually entering thousands of rows, which is why I'm coming to you guys. Thanks in advance, I appreciate the help.

 

[RasGhul]

Hi Sammi,

See if the following suits, it would be better to change your data tab into an excel table which will make the lookup formulas update dynamically without the need to change ranges length.

I can re-post formulas with this change if you need.

Return a value based on two criterias_Sammi8796.xlsx
	A	B	C	D	E	F	G	H	I
1	Item		Date	Num	memo	name	item description	Qty	sales price
2	1101004		30/10/2018	154688		Customer1	item4		$729.95
3	1101004		11/02/2019	157637		Customer2	item4		$729.95
4	1101004		13/02/2018	173047		Customer3	item4		$729.95
5	1101006		2/11/2018	154652		Customer4	item6		$835.95
6	1101006		29/04/2019	161469		Customer5	item6		$735.64
7	1101006		9/06/2020	177982		Customer6	item6		$771.00
8	1101006		12/06/2020	178196		Customer7	item6		$771.00
9	1302001		18/02/2019	157900		Customer8	item13		$691.70
10	1302001		20/02/2019	158017		Customer9	item13		$691.70
11	1302001		29/04/2019	161469		Customer10	item13		$691.70
12	1302001		15/05/2019	162264		Customer11	item13		$691.70
13	1400001		2/05/2019	161820		Customer12	item14		$65.00
14	1400001		9/06/2020	177981		Customer13	item14		$77.00
15	1400001		2/01/2020	171324		Customer14	item14		$57.20
16	1400001		13/03/2021	189854		Customer15	item14		$77.00
Data

Return a value based on two criterias_Sammi8796.xlsx
	A	B	C	D	E	F	G
4	item number	Description	Number Sold	AveragePrice	Total Sales	Last Sale Date	Last Customer to Purchase
5	1101004	item4		$729.95	$2,189.85	11/02/2019	Customer2
6	1101006	item6		$778.40	$3,113.59	12/06/2020	Customer7
7	1302001	item13		$691.70	$2,766.80	15/05/2019	Customer11
8	1400001	item14		$69.05	$276.20	13/03/2021	Customer15
Lookup

Cell Formulas
Range	Formula
D5:D8	D5	=AVERAGEIFS(Data!$I$2:$I$16,Data!$A$2:$A$16,Lookup!A5,Data!$I$2:$I$16,">0")
E5:E8	E5	=SUMIFS(Data!$I$2:$I$16,Data!$A$2:$A$16,Lookup!A5)
F5:F8	F5	=MAX(IF(Data!$A$2:$A$16=A5,Data!$C$2:$C$16))
G5:G8	G5	=IFNA(INDEX(Data!$F$2:$F$16,MATCH(Lookup!A5&Lookup!B5&Lookup!F5,INDEX(Data!$A$2:$A$16&Data!$G$2:$G$16&Data!$C$2:$C$16,),0)),"")
B5:B8	B5	=IFNA(INDEX(Data!$G$2:$G$16,MATCH(Lookup!A5,Data!$A$2:$A$16,0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.