CODEWITHSUNDEEP
arraysexcelformulavlookupspreadsheet
Kari Chisholm
Kari Chisholm

Reputation: 11

Excel formula: picking a value from a table/array of non-unique values

OK, so here's the problem that's got me stumped in Excel. I have three columns:

date    value   fruit
5-Jan   19  apple
5-Jan   2   banana
6-Jan   8   grapefruit
6-Jan   2   lemon
6-Jan   14  orange
5-Jan   8   peach
1-Jan   14  pear
6-Jan   4   starfruit
10-Jan  3   strawberry

The data is not sorted in any way. The dates, values, and fruits are non-unique.

I'm going to copy and paste the date values into the first column of another table, and remove duplicates. I need a formula to paste into the second column of that table that will, for each date, identify the fruit with the highest value.

Here's the expected outcome from that formula.

date    fruit
1-Jan   pear
5-Jan   apple
6-Jan   orange
10-Jan  strawberry

I've been playing around with index/match, vlookup, arrays (which baffle me), and I'm stumped.

Ideas? Much thanks!

Upvotes: 1

Views: 290

Answers (1)

Harun24hr
Harun24hr

Reputation: 36840

With EXCEL-365 try below

=@INDEX(SORT(FILTER($B$2:$C$10,$A$2:$A$10=F2),1,-1),,2)

For older version of excel try below array formula-

=INDEX($C$2:$C$10,SMALL(IF($A$2:$A$10=F2,IF($B$2:$B$10=MAX(IF($A$2:$A$10=F2,$B$2:$B$10,"")),ROW($B$2:$B$10)-ROW($B$1),""),""),1))

Array formula needs CSE entry means press CTRL+SHIFT+ENTER.

enter image description here

Upvotes: 1

Related Questions