I have hundreds of rows of data coming in regularly and need an answer too quickly to sort the data first. Can you assist me in coming up with a formula that finds the nth largest number in a range based on criteria in another column? For example, how would I get the the second largest value in the second column only for those rows that contain the word "Apple" in the first column (answer: 7)? I have Excel 365 but may have some colleagues that have older version of Excel.
Apple | 5 |
Banana | 3 |
Cherry | 8 |
Orange | 5 |
Blueberry | 0 |
Banana | 2 |
Orange | 6 |
Apple | 9 |
Orange | 1 |
Apple | 7 |
Cherry | 1 |
Cherry | 8 |
Blueberry | 5 |
Apple | 1 |