Hi All,
I'm running into a real challenge looking up corresponding fields in a table with several records of data. I have table (Excel sheet attached) which contains a unique key in column A. Within the key the table is is sorted on column B (Value1). I've been able to look up the top 5 largest values in column D (Diff) where the condition in column E matches 'Red' by using an array version of the Large function.
The top 5 largest values in the Diff column where Condition meets 'Red' are 50, 50, 40, 40 and 30 (rows 4, 7, 3, 10 and 9). I'm looking for a formula to retrieve the Key values in column A. As the table is not sorted on the Diff column and the Diff column can contain duplicates, I'm having trouble finding a formula to retrieve the corresponding Key values (Column A) of the rows of the top 5 largest fields which match the specified condition.
Key Value1 Value2 Diff Condition
1 100 60 40 Black
2 99 49 50 White
3 98 58 40 Red
4 97 47 50 Red
5 96 76 20 Black
6 95 65 30 White
7 94 44 50 Red
8 93 23 70 White
9 92 62 30 Red
10 91 51 40 Red
Any help will be greatly appreciated!
Thanks in advance, Eric
Bookmarks