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.
Task
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
Reply to this post with your formula code and solution file. Please be sure to blur or hide your formula code.
=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
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
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.
@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 ).