CODEWITHSUNDEEP
excelexcel-formula
Phyo Min Yu
Phyo Min Yu

Reputation: 153

Finding Max Values depending on Lookup Values

I have Sale Invoices for bread, jam, etc. like this table.

+-------+--------+-------+
| Item  | Date   | Price |
+-------+--------+-------+
| Bread | 1-Dec  | 5     |
+-------+--------+-------+
| Jam   | 1-Dec  | 5     |
+-------+--------+-------+
| Bread | 8-Dec  | 6     |
+-------+--------+-------+
| Jam   | 8-Dec  | 4     |
+-------+--------+-------+
| Bread | 15-Dec | 4     |
+-------+--------+-------+
| Jam   | 15-Dec | 7     |
+-------+--------+-------+

I want the highest price date for each item like

+-------+--------+---------------+
| Item  | Date   | Highest Price |
+-------+--------+---------------+
| Bread | 8-Dec  | 6             |
+-------+--------+---------------+
| Jam   | 15-Dec | 7             |
+-------+--------+---------------+

It is like finding Max Values depending on Lookup Values. It is very much like Group By and Max in SQL. How do I do it in excel? I've tried index match and also googling. Nothing helps. Please help me.

Upvotes: 0

Views: 94

Answers (1)

JvdV
JvdV

Reputation: 75840

This is typically done through a pivot table:

enter image description here

  • Select your data.
  • Insert a pivot table.
  • In your case use "Item" & "Date" as rows.
  • In your case use "Price" as value.
  • Then click "Price" and under it's field settings choose "Max".
  • Then in the pivot table itself right any date, click "Filter" > "Top Ten" and make that top 1 based on the max price.

There are many ways to do this through formulae, but if one has Excel O365 it can be done through one single formula, for example:

enter image description here

Formula in E2:

=TRANSPOSE(CHOOSE({1,2,3},TRANSPOSE(UNIQUE(A2:A7)),TRANSPOSE(MINIFS(B2:B7,A2:A7,UNIQUE(A2:A7),C2:C7,MAXIFS(C2:C7,A2:A7,UNIQUE(A2:A7)))),TRANSPOSE(MAXIFS(C2:C7,A2:A7,UNIQUE(A2:A7)))))

Or:

=FILTER(A2:C7,ISNUMBER(MATCH(A2:A7,UNIQUE(A2:A7),0))*LET(X,MAXIFS(C2:C7,A2:A7,UNIQUE(A2:A7)),ISNUMBER(MATCH(C2:C7,MAXIFS(C2:C7,A2:A7,UNIQUE(A2:A7))))*ISNUMBER(MATCH(B2:B7,MINIFS(B2:B7,A2:A7,UNIQUE(A2:A7),C2:C7,X),0))))

Upvotes: 1

Related Questions