In MS Excel, you have a list of sales data in column A, and you need to find the second highest unique value in this list. Which of the following formulas will correctly return the second highest unique value?

A =LARGE(A:A, 2)
B =LARGE(UNIQUE(A:A), 2)
C =INDEX(SORT(UNIQUE(A:A), , -1), 2)
D =SMALL(UNIQUE(A:A), 2)

C. =INDEX(SORT(UNIQUE(A:A), , -1), 2) is the correct formula to find the second highest unique value in column A of your MS Excel data.

Here’s why each option works (or doesn’t):

• A. =LARGE(A:A, 2): This formula uses the LARGE function, which returns the nth largest value in a range. However, it doesn’t consider uniqueness. So, if there are duplicates of the highest value, it might not be the second truly unique highest value.
• B. =LARGE(UNIQUE(A:A), 2): This formula is on the right track! It uses UNIQUE to remove duplicates and then LARGE to find the second largest value within the unique list. However, there’s a simpler way.
• C. =INDEX(SORT(UNIQUE(A:A), , -1), 2): This is the champion! It breaks down like this:
  • UNIQUE(A:A) removes duplicates from column A.
  • SORT(..., , -1) sorts the unique values in descending order (largest to smallest) because -1 indicates descending order.
  • INDEX(...) picks the value at a specific position (the second one in this case, indicated by 2).
• D. =SMALL(UNIQUE(A:A), 2): This formula uses SMALL which finds the nth smallest value. We need the highest unique value, not the smallest, so this isn’t the answer.

Therefore, formula C efficiently combines removing duplicates, sorting, and selecting the second largest value in a single expression.