CODEWITHSUNDEEP
excelexcel-formulavlookup
AlliDeacon
AlliDeacon

Reputation: 1495

Excel -- Pull the most recent value for an Account / Item Value pair

I have an excel spreadsheet that has two tabs. The First Tab has Customer Item sales for a date range, including sale date. so columns on the 1ST tab are

CUSTOMER | ITEM | DATE | QTY | PRICE | EXTENSION

Test Data:

Customer No.    Date     Item     Qty Ship  Price   Ext. 
100207        8/19/2015  7044503    3       90.85   272.55
100207        8/12/2015  7044503    10     90.85    908.5
100207        8/12/2015  7044504    16     91.99    1471.84
100207        7/31/2015  7044505    9      93.41    840.69
100207        7/31/2015  7045427    2         73    146
112179        7/22/2015  6951235    1      23.65    23.65
112179        7/22/2015  6951235    1      23.65    23.65
112179        7/15/2015  6951235    1      23.65    23.65
112179         7/8/2015  6951235    1      23.65    23.65
112179        6/30/2015  6951235    1      23.65    23.65
112179        4/22/2015 0290130075  9       2.75    22.86
112179        9/23/2015 0290130075  9       2.54    22.86
112179         9/9/2015 0290130075  9       2.40    22.86
112308         9/9/2015 VWR40101    1     451.00   451.00
112308        8/31/2015 VWR40101    1     451.00   451.00
112308        8/26/2015 VWR65020    3     186.00   558.00
112308        8/12/2015 VWR65020    2     167.75    335.5
112308        7/31/2015 VWR65020    2     175.00    350.0
112308        7/22/2015 VWR65020    4     177.75    711.0

The columns on the 2nd tab are

CUSTOMER | ITEM | TOTAL SALES

Test Data:

Customer No.    Item      Total
100207          7044503   1181.05
100207          7044504   1471.84
100207          7044505    840.69
100207          7045427    146
112179          6951235    118.25
112179          290130075   68.58
112308          VWR40101    902
112308          VWR65020    1954.5
Grand Total                 6682.91

I am trying to add another column to the 2nd tab for LAST SALE PRICE

I know it's likely a comination of MAX(), MATCH(), AND VLOOKUP() but I'm not sure how to put them together to accomplish my goal.

Thanks!

Upvotes: 0

Views: 119

Answers (2)

tigeravatar
tigeravatar

Reputation: 26670

Regular (non-array) formula, adjust ranges to suit your data:

=INDEX(Sheet1!$E$2:$E$20,MATCH(1,INDEX((Sheet1!$A$2:$A$20=A2)*(Sheet1!$C$2:$C$20=B2)*(Sheet1!$B$2:$B$20=MAX(INDEX((Sheet1!$A$2:$A$20=A2)*(Sheet1!$C$2:$C$20=B2)*Sheet1!$B$2:$B$20,))),),0))

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152660

Try this array formula

=INDEX(Sheet1!$E$2:$E$100,MIN(IF((Sheet1!$A$2:$A$100 = A2)*(Sheet1!$B$2:$B$100 = B2)*(Sheet1!$C$2:$C$100 = MAX(IF((Sheet1!$A$2:$A$100 = A2)*(Sheet1!$B$2:$B$100 = B2),Sheet1!$C$2:$C$100))),(ROW(Sheet1!$A$2:$A$100)-1))))

Without test data it is hard to test.

Being an Array formula it must be confirmed with Ctrl-Shift-Enter when exiting edit mode. If done correctly Excel will put {} around the formula.

If you are using excel 2010 or later then you can use this formula:

=INDEX(Sheet1!$E$2:$E$100,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$100)-1)/((Sheet1!$A$2:$A$100 = A2)*(Sheet1!$B$2:$B$100 = B2)*(Sheet1!$C$2:$C$100 = AGGREGATE(14,6,Sheet1!$C$2:$C$100/((Sheet1!$A$2:$A$100 = A2)*(Sheet1!$B$2:$B$100 = B2)),1))),1))

Upvotes: 1

Related Questions