Excel Workout 12 - XLookup & ToRow Functions!

Workouts Excel
1

Welcome to Excel Workout #12!
Difficulty Level:

Stars

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

image
image1677×439 37.3 KB

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.

Goals
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.

Task

  • 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.

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

image
image735×266 25.6 KB

Period
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

2

Here is my file

Challenge #12.xlsx (1.7 MB)

image

1 Like
3

So great!

4

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.


=XLOOKUP(E9,TOROW(A3:C5),TOROW(E3:G5))


=XLOOKUP(E9,TOCOL(A3:C5),TOCOL(E3:G5))

Challenge #12_Quadri_Atharu.xlsx (8.3 MB)

1 Like
5

Love the combination of XLookup and ToRow

image
image1275×355 20.3 KB

1 Like
6

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

=XLOOKUP($E$9,TOROW($A$3:$C$5),TOROW($E$3:$G$5))

image
image1761×451 29.8 KB

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

Thanks for the workout. :slight_smile:
Keith

1 Like
7

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:

image
image1683×524 78 KB

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!

8

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

=XLOOKUP(E9,TOROW(A3:C5),TOROW(E3:G5))

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

IMG_4982
IMG_49821705×924 197 KB

1 Like
9

@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
10

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