Scenario: Let’s say you have data where on various dates employees are reporting some expense/dollar amounts. You want a formula that identifies which EmployeeID in column C has the highest dollar amount value in column D.

This is a complex formula that requires at least three functions to find the answer. Most people resort to doing this manually as they may not know how to combine (“Nest”) multiple functions. You can use the MAX function to find the highest value, but what function do you use to find the EmployeeID in column C?

VLOOKUP performs a left-to-right directional lookup, which is in the opposite direction in this scenario. XLOOKUP may be used but it is only available in Excel 2021 and Excel 365, a subscription-based version (users of Excel 2019 and earlier are out of luck).

A manual method: You can SORT column D (Amount) in descending order, and then visually look at column C to find the EmployeeID.

Problem: What happens if you are frequently adding data to the table, and you always need to know who is reporting the highest amount? Manually performing the step is tedious and inefficient, requiring you to undo the SORT, or SORT BY date, to get back to the original order/sequence.

The ideal solution is to find the complex formula that does it all for you. ChatGPT is an excellent tool in this situation. Simply go to the ChatGPT website, type the question, and let ChatGPT give you the answer/formula that you can Copy & Paste into Excel (verbatim). The screenshots below to demonstrate the power of ChatGPT tool to find solutions.

 

The words highlighted in yellow are the literal words that were written into the ChatGPT search box. The blue highlighted text is the actual formula that was produced by ChatGPT, and along with an explanation of the functions. Simply copy and paste the formula into Excel. The result is the EmployeeID with the highest amount that you were searching for.

 

Additional Tip

Use the conditional formatting feature in Excel to highlight the answer found by the ChatGPT formula (as shown by the circled items).

Nesting complex functions and conditional formatting are topics covered in Excel Level 2 and Excel Level 3.