Excel Workout 12 - XLookup & ToRow Functions!

Welcome to Excel Workout #12!
Difficulty Level:


This week’s challenge is designed to test your knowledge on XLookup & ToRow Functions.

XLookup Function
The XLOOKUP function is a powerful search function in Microsoft Excel that allows you to search for a specific value in a range or array, and return a corresponding result from another column or array. The “X” in XLOOKUP stands for “extended,” as it offers more advanced capabilities than the traditional VLOOKUP and HLOOKUP functions.

ToRow Function
The TOROW function in Excel is used to convert an array or range of cells into one row. The function takes a total of three arguments, of which only the first one is required.

Please follow the directions given below, which include downloading the Excel worksheet required to perform the challenge tasks. Once you have completed the download, proceed to take the challenge and test your skills.


  • In cell F9, enter the name of the capital city for the country selected in cell E9, utilizing the Xlookup and Torow functions. The countries are listed in the A3:C5 range and the corresponding capitals are listed in the E3:G5 range.

Reply to this post with your formula code and solution file. Please be sure to blur or hide your formula code.

This workout will be released on Thursday May 04, 2023 , and the author’s solution will be posted on Wednesday May 10, 2023 .

Challenge #12.xlsx (8.3 MB)

Good luck,

Ilgar Zarbaliyev

Here is my file

Challenge #12.xlsx (1.7 MB)


1 Like

So great!

This one is quite straightforward and easy. It could be achieved in so many ways. But with TOROW and TOCOL function. it is quite easy.



Challenge #12_Quadri_Atharu.xlsx (8.3 MB)

1 Like

Love the combination of XLookup and ToRow

1 Like

Hi @IlgarZarbaliyev,

thanks for the workout. I’ve used Xlookup before but never had to use ToRow Function before.

I had to review both functions


Excel Workout Challenge #12 Keith Smith.xlsx (8.3 MB)

Thanks for the workout. :slight_smile:

1 Like

Thank you for participating in the Excel Challenge related to Xlookup & ToRow Functions! I hope you found this challenge to be a fun and engaging way to improve your Excel skills and learn more about how to work with Xlookup & ToRow Functions in Excel.

Here is my solution to Workout #12:

Challenge #12 with Solution.xlsx (8.3 MB)

Once again, thank you for participating in this Excel Challenge, and I look forward to seeing you in future challenges and learning opportunities!

Here’s my submission. I must say these are 2 formulas I never used when I was a deep excel user


Of course I want to see what chatGPT could do here. Was bang on I even help describe what was happening.

It got it kind of incorrect but very close. Maybe it got confused by the data format when copied in. I’m not sure

1 Like

@IlgarZarbaliyev - For some reason, I am not getting the TOROW() function in my Excel workbook. Could this be related to the version of Excel that I am using? If the function is unavailable, is there an alternative way to achieve the same functionality?

FYI - Currently I am using Microsoft® Excel® LTSC MSO (Version 2307 Build 16.0.16626.20086) 64-bit

1 Like

The TOROW function is part of the new Excel Dynamic Arrays family. At the time of writing, Dynamic Arrays are only available in Microsoft 365. Excel 2021 and earlier Excel versions will not get TOROW.

1 Like