I need to find the three highest values in a column and return these values, along with corresponding text in non-adjacent cells, to another area of the spreadsheet.
In cell Z5, I'm using the large function to find the highest value: =LARGE($G$4:$G$29,1) (Z6 and Z7 contain the next higher numbers in descending order).
In cell AA5 I'm using this array to return the text: =IF(ROWS($2:$2)<=COUNTIF($G$4:$G$29,Z5),INDEX($B$4:$B$29,SMALL(IF($G$4:$G$29=Z5,ROW($G$4:$G$29)-MIN(ROW($G$4:$G$29))+1),ROWS($2:$2))),"")
The text is located in column B, the numbers in column G.
The two formulas work until the large function returns duplicate numbers. Then the text to the first number is repeated as the text for the second duplicate number. How do I alter this formula (or write a simpler one) to ensure the corresponding text is copied with the duplicate numbers?
Many thanks for the help!
Bookmarks