Return a value based on two criterias

Sammi8796

Board Regular
Joined
Dec 12, 2007
Messages
100
Office Version
  1. 2016
Platform
  1. Windows
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:
sample.jpg



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:

Lookup Tab.jpg


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.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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
ABCDEFGHI
1ItemDateNummemonameitem descriptionQtysales price
2110100430/10/2018154688Customer1item4$729.95
3110100411/02/2019157637Customer2item4$729.95
4110100413/02/2018173047Customer3item4$729.95
511010062/11/2018154652Customer4item6$835.95
6110100629/04/2019161469Customer5item6$735.64
711010069/06/2020177982Customer6item6$771.00
8110100612/06/2020178196Customer7item6$771.00
9130200118/02/2019157900Customer8item13$691.70
10130200120/02/2019158017Customer9item13$691.70
11130200129/04/2019161469Customer10item13$691.70
12130200115/05/2019162264Customer11item13$691.70
1314000012/05/2019161820Customer12item14$65.00
1414000019/06/2020177981Customer13item14$77.00
1514000012/01/2020171324Customer14item14$57.20
16140000113/03/2021189854Customer15item14$77.00
Data


Return a value based on two criterias_Sammi8796.xlsx
ABCDEFG
4item numberDescriptionNumber SoldAveragePriceTotal SalesLast Sale DateLast Customer to Purchase
51101004item4$729.95$2,189.8511/02/2019Customer2
61101006item6$778.40$3,113.5912/06/2020Customer7
71302001item13$691.70$2,766.8015/05/2019Customer11
81400001item14$69.05$276.2013/03/2021Customer15
Lookup
Cell Formulas
RangeFormula
D5:D8D5=AVERAGEIFS(Data!$I$2:$I$16,Data!$A$2:$A$16,Lookup!A5,Data!$I$2:$I$16,">0")
E5:E8E5=SUMIFS(Data!$I$2:$I$16,Data!$A$2:$A$16,Lookup!A5)
F5:F8F5=MAX(IF(Data!$A$2:$A$16=A5,Data!$C$2:$C$16))
G5:G8G5=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:B8B5=IFNA(INDEX(Data!$G$2:$G$16,MATCH(Lookup!A5,Data!$A$2:$A$16,0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top