𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄e𝗿:
You have 2 minutes to solve this Excel problem.

You have the following data:

Employee Department Salary

John IT 75,000
Sarah HR 60,000
Mike IT 82,000
David Finance 90,000
Alice HR 65,000


How would you find the second highest salary in the IT department?

𝗠𝗲: Challenge accepted! 💪

=LARGE(FILTER(C2:C6,B2:B6="IT"),2)

💡 Explanation:

This formula combines FILTER() and LARGE() to find the second highest salary within a specific department.

- FILTER(C2:C6,B2:B6="IT") returns only salaries from the IT department.
- LARGE(...,2) returns the second largest value from those salaries.

The result is 75,000.


This challenge tests your understanding of: ✅ FILTER()
✅ LARGE()
✅ Conditional Filtering
✅ Combining Excel Functions


🚀 Bonus (Without FILTER)

For older Excel versions, you can use:

=AGGREGATE(14,6,C2:C6/(B2:B6="IT"),2)

Here:

14 represents LARGE.

6 ignores errors.

B2:B6="IT" filters the calculation to the IT department.

2 returns the second largest value.


❤️ React with ❤️ for more Excel interview challenges!