Using Xlookup Formula to find the highest value in Excel

In the previous training video, we used the INDEX-MATCH formula to find the month with the highest sales. In this video, let’s explore how to achieve the same result using the new Excel function XLOOKUP.

Here’s how you can do this step-by-step:

1. Start by typing =XLOOKUP( in the cell where you want the result.
2. The first parameter asks what value you’re searching for. We’ll use the maximum sales value. Type MAX( and select the sales data range. Close the parenthesis.
3. Add a comma (, or ; depending on your Excel settings—if Excel suggests comma, use comma; if it shows semicolon, use semicolon).
4. The second parameter is the range where you’re searching for this maximum value. Select your sales data range again and add another comma or semicolon.
5. The third parameter specifies the range containing the result you want to retrieve. In our case, this will be the month names. Select the months range here.
   Important: After selecting the months range, press F4 on your keyboard to lock this reference (use Fn + F4 if you’re using a laptop).
6. Close the parenthesis and press Enter. The formula will return the month corresponding to the highest sales figure.

Here’s the formula structure clearly shown:

=XLOOKUP(MAX(sales range), sales range, months range)

By using the XLOOKUP function, you can easily find the month corresponding to the highest sales figure in your dataset. If your Excel version doesn’t support XLOOKUP, you can achieve the same result with the INDEX-MATCH function, as demonstrated in the previous video.

Don’t forget to like the videos on my channel, subscribe, comment, and share. See you in the next Excel training video!