0

My question is similar to one asked here, but I am having trouble making this work for my situation given my data. I have a data set that uses seeded numbers in row 1 that I use to index match columns. This is because there are drop-down menus that change the match column based on user selection. So the the columns cannot be directly referenced. My data very roughly looks like this:

         45      46      50     28  

Route   
CCS     500      325     40    200
CCS     370      100    380     10
RCS      90      825     50    999
CCS     100       50     32    358

So when my user makes a selection, the number in AE2 changes to reflect the column seed I want (in example, either 45, 46, 50, or 28). I want to be able to return the Kth largest number in that column that is also "CCS". So lets say the user chooses 46 and I want the 2nd largest number that has "CCS" in Route. So the formula searches row 1 for "46", then once it finds the column with it, it looks down that column for the 2nd largest CCS value -- which is 100. I have tried to modify the formula suggested in the other question, (below) but that just seems to stop at the first observation, and I need it to search all of the observations. 

LARGE(IF( 'Program Data'!O:O="CCS", INDEX('Program Data'!$A:$GB,0,(MATCH($AE$2,'Program Data'!$1:$1,0)))),1)

Any tips as to what I'm doing wrong?

Community
  • 1
  • 1
Mike L
  • 486
  • 5
  • 16
  • 33

1 Answers1

0

Your formula works for me....but it's an "array formula" so you need to confirm with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula

barry houdini
  • 45,615
  • 8
  • 63
  • 81