2

I am trying to produce a sorted table in excel, which depend on the selected year and category.

My methodology has been to sequentially find largest values in order, within the selected year and category parameters, doing the following:

Column E

{=LARGE(IF(('Master Data'!A$1:A$500 = $B$1) * ('Master Data'!B$1:B$500 = $B$2),'Master Data'!C$1:C$500), $B10)}

This works fine, $B$1$ is where I store the year, $B$2 is where I store the category, $B10 references a hard coded 1-25 in column B.

Column F

{=MATCH(E10,IF(('Master Data'!A$1:A$500 = $B$1) * ('Master Data'!B$1:B$500 = $B$2),'Master Data'!C$1:C$500),FALSE)}

This returns the row number of the result I need, which I then use in conjunction with INDEX to find related data.

The problem with this is that Match only returns the first row number, and if you have two results with the same value this clearly becomes an issue.

Column G

To resolve this I used an example from dailydoseofexcel which looks like this:

=IF(F10<>F11, F11, G10+MATCH(E11,INDIRECT("'Master Data'!C"&(G10+1)&":C500"),0))

This works to a limited extent, for my purposes, as it is unable to take into account the year and category filter I need to apply, so I tried:

{=IF(F10<>F11, F11, G10+MATCH(E11,IF((INDIRECT("'Master Data'!A"&(G10+1)&":A500") = $C$2) * (INDIRECT("'Master Data'!B"&(G10+1)&":B500") = $C$3), INDIRECT("'Master Data'!C"&(G10+1)&":C500")),0))}

But I am just getting #N/A as a result.

Ram
  • 3,092
  • 10
  • 40
  • 56
Steven
  • 721
  • 6
  • 23
  • What you want to do is sort a list but put your selected year and category on top, correct? Do you worry about how the rest of the items are sorted? – LeppyR64 May 18 '14 at 23:14

1 Answers1

0

I think SUMPRODUCT may be what you are looking for:

Charley Kyd XL Legend: Use SUMPRODUCT to get the Last item in a list

Our Man in Bananas
  • 5,809
  • 21
  • 91
  • 148