# Excel Workout 23 - INDEX Function (CEV EuroVolley Challenge)!

Welcome to Excel Workout #23!
Difficulty Level:

This week’s CEV EuroVolley Challenge is designed to test your knowledge on INDEX Function.

Photo Source: Photos | EuroVolley (cev.eu)

INDEX Function
The INDEX function is a powerful and versatile function in Microsoft Excel that allows you to retrieve a value or a reference to a value from within a specified range or array. It is commonly used to extract data from a table or a range based on specified criteria.

• Incorporating the Index function, input the player’s date of birth, Country, club, and picture from the chosen cell B1 into cells B2, B3, B4, and B5. Data is given in DATA sheet of Challenge 23 file.

Submission

Period
This workout will be released on Sunday June 11, 2023, and the author’s solution will be posted on Sunday June 18, 2023.

Challenge 23.xlsx (21.7 MB)

Good luck,

Ilgar Zarbaliyev

1 Like

1 Like

This one a little tricky, getting the photo to come in but thanks to Youtube and ChatGPT. I figured it out.

Birthday:

=INDEX(Data[BIRTHDATE],XMATCH(Research!B1,Data[NAME]))

Country:

=INDEX(Data[COUNTRY],XMATCH(Research!B1,Data[NAME]))

Club:

=INDEX(Data[CLUB],XMATCH(Research!B1,Data[NAME]))

Photo:

a tricky one getting the photo, rangename etc to work
=INDEX(DATA!\$O\$2:\$O\$337,MATCH(Research!\$B\$1,DATA!\$C\$2:\$C\$337,0))

Just wish there was a Canada Team there

I haven’t used the photo/image for a long time, it was good to refresh the skills sets.

File Attached:
Excel Workout Challenge 23 Keith.xlsx (22.0 MB)

thanks for the workout.
Keith

1 Like

Thank you for your solution, @Keith. As challenge is related to CEV EuroVolley, Canada Teams has been included:(. Next time:)

Birthday:

=INDEX(Data[BIRTHDATE],XMATCH(\$B\$1,Data[NAME]))

Country:

=INDEX(Data[COUNTRY],XMATCH(\$B\$1,Data[NAME]))

Club:

=INDEX(Data[CLUB],XMATCH(\$B\$1,Data[NAME]))

Player Picture:

=INDEX(DATA!\$O\$2:\$O\$337,MATCH(Research!\$B\$1,DATA!\$C\$2:\$C\$337,0))

1 Like

Here is my file:

Challenge 23.xlsx (15.2 MB)

1 Like

``````

[spoiler]
Birthday = INDEX(Data[BIRTHDATE],MATCH(Research!\$B\$1,Data[NAME],0))
Country = INDEX(Data[COUNTRY],MATCH(Research!\$B\$1,Data[NAME],0))
Club      = INDEX(Data[CLUB],MATCH(Research!\$B\$1,Data[NAME],0))
Picture  =INDEX(DATA!\$O\$2:\$O\$337,MATCH(Research!\$B\$1,DATA!\$C\$2:\$C\$337,0))
[/spoiler]

1 Like
``````=INDEX(Data[BIRTHDATE];MATCH(Research!B1;Data[NAME];0))
=INDEX(Data[COUNTRY];MATCH(Research!B1;Data[NAME];0))
=INDEX(Data[CLUB];MATCH(Research!B1;Data[NAME];0))
Xmatch could also be used
``````

=INDEX(DATA!\$O\$2:\$O\$337;MATCH(Research!\$B\$1;DATA!\$C\$2:\$C\$337;0))

1 Like

Country: =INDEX(DATA!\$A:\$O,MATCH(Research!\$B\$1,DATA!\$C:\$C,0),2)
Club: =INDEX(DATA!\$A:\$O,MATCH(Research!\$B\$1,DATA!\$C:\$C,0),13)
Picture: =INDEX(DATA!\$A:\$O,MATCH(Research!\$B\$1,DATA!\$C:\$C,0),15)

1 Like

Hi to all!

Two Formulas used for this challenge:
[B2]: =INDEX(Data,XMATCH(B1,Data[NAME]),{4;2;13})
[B5]: =Picture

Name Picture: =INDEX(Data[PHOTO],XMATCH(Research!\$B\$1,Data[NAME]))

Blessings!
Challenge 23.xlsx (15.4 MB)

1 Like

Challenge 23 - Oqtay.xlsx (10.6 MB)

``````
`
``````
1 Like

Hi @IlgarZarbaliyev , Thanks for the challenge. Please see my solution. I always prefer using INDEX and MATCH to vLookup as I find it far more versatile.

``````Birthday: =INDEX(DATA!D:D,MATCH(B\$1,DATA!\$C:\$C,0))
Country: =INDEX(DATA!B:B,MATCH(B\$1,DATA!\$C:\$C,0))
Club:  =INDEX(DATA!M:M,MATCH(B\$1,DATA!\$C:\$C,0))
Picture: Created a Named Range for the picture called PlayerPics:  =INDEX(DATA!P:P,MATCH(Research!\$B\$1,DATA!\$C:\$C,0)) and then made the Picture cell =PlayerPics
``````
1 Like

Thank you for participating in the Excel Challenge related to INDEX Function (CEV EuroVolley Challenge)!
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 INDEX Function (CEV EuroVolley Challenge)! in Excel.

Here is my solution to Workout #23!

Solution

• Birthday:
=INDEX(DATA!\$D\$2:\$D\$337,MATCH(Research!\$B\$1,DATA!\$C\$2:\$C\$337,0))

• Country:
=INDEX(DATA!\$B\$2:\$B\$337,MATCH(Research!\$B\$1,DATA!\$C\$2:\$C\$337,0))

• Club:
=INDEX(DATA!\$M\$2:\$M\$337,MATCH(Research!\$B\$1,DATA!\$C\$2:\$C\$337,0))

• Picture

1. =PlayersPictures

2. =INDEX(DATA!\$O\$2:\$O\$337,MATCH(Research!\$B\$1,DATA!\$C\$2:\$C\$337,0))

Challenge 23 with Solution.xlsx (21.9 MB)

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

``````[spoiler]
[details="Summary"]

[/details]
[/spoiler]
``````
1 Like

Summary

` [Challenge 23 Rosi.xlsx|attachment](upload://2SK05S6YvY4R2w2eojnCHQnqB85.xlsx) (21.9 MB) Hope now it's okay`

Unfortunately, not…

Challenge 23 Rosi.xlsx (21.9 MB)

1 Like

@IlgarZarbaliyev - The last part of the workout displaying the player image was a bit tricky.

Formula to show:
a) Birthday: =INDEX(Data[BIRTHDATE],MATCH(\$B\$1,Data[NAME],0))

b) Country: =INDEX(Data[COUNTRY],MATCH(\$B\$1,Data[NAME],0))

c) Club: =INDEX(Data[CLUB],MATCH(\$B\$1,Data[NAME],0))

d) Player Image: I copied any one of the player’s image and paste it in the given area. Then I clicked on it and in the formula bar I simply typed =PlayersPictures ( a named range which was already present ).

Snapshot of the result:

And here’s my workout file: Challenge 23.xlsx (21.8 MB)

1 Like