Excel Workout 21 - Index Function!

[IlgarZarbaliyev]

Welcome to Excel Workout #21!
Difficulty Level:

This week’s challenge is designed to test your knowledge on Index Function.

INDEX Function
The INDEX function in Excel is a built-in function that allows you to retrieve the value from a specified position within a range or array.

Task

We possess a dataset that includes four print areas: Apples, Bananas, Lemons, and Pears. In cell C2, we can choose quarters; in cell C3, regions; and in cell C4, print areas. The Switch function converts the selected data in column C into numerical values in respective cells in D Column.

• Cell Q17 should display the required information according to the selected details in cells C2, C3, and C4.

• Simultaneously, display the data in Cells P20:S20 for all regions based on the selections made in cells C2 and C4.

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

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

Challenge #21.xlsx (8.3 MB)

Good luck,

Ilgar Zarbaliyev

[Tural]

Thank you for an interesting challenge, @IlgarZarbaliyev. Here is my solution.

Summary

Function for Q17:
=INDEX(CHOOSE($D$4, I3:L6, O3:R6, I10:L13, O10:R13), $D$2, $D$3)

P20:
=INDEX(CHOOSE($D$4, $I$3:$L$6, $O$3:$R$6, $I$10:$L$13, $O$10:$R$13), $D$2, 1)

Q20:
=INDEX(CHOOSE($D$4, $I$3:$L$6, $O$3:$R$6, $I$10:$L$13, $O$10:$R$13), $D$2, 2)

R20:
=INDEX(CHOOSE($D$4, $I$3:$L$6, $O$3:$R$6, $I$10:$L$13, $O$10:$R$13), $D$2, 3)

S20:
=INDEX(CHOOSE($D$4, $I$3:$L$6, $O$3:$R$6, $I$10:$L$13, $O$10:$R$13), $D$2, 4)

Challenge #21_TuralMammadov.xlsx (8.3 MB)

[ShirleyMoreman]

Great challenge using some interesting parts of INDEX that probably don’t get used very often.

My solution attached
Challenge 21 ShirleyMoreman.xlsx (8.3 MB)

[IlgarZarbaliyev]

Thank you for your participation, @ShirleyMoreman

[jany.gibson]

Hi Ilgar:
Here is my solution.

Regarding this subtask:
“Simultaneously, display the data in Cells P20:S20 for all regions based on the
elections made in cells C2 and C4”
Kindly look at the formula in cell P20, despite not adding the column (the sales region) as the task only asks to reference cells C2 & C4, the formula works but I don’t know why, how is it determining the column?
Also, when did the Index function become dynamic? I see the active cell in P20 and greyed out inQ20…S20.

Challenge #21 Index-Franziola Chiu.xlsx (8.3 MB)

Thanks,
Franziola

[BALBRECHT]

I used named ranges Apples, Bananas, Lemons and Pairs
then used the indirect function to reference the named range and used index to move around.

Apples = $I$3:$l$6 
Bananas = $O$3:$R$6
Lemons = $I$10:$L$14
Pairs = $O$10:$R$14

Q17 =INDEX(INDIRECT($C$4),$D$2,$D$3)

P20 = INDEX(INDIRECT($C$4),$D$2,1)
Q20 = INDEX(INDIRECT($C$4),$D$2,2) 
R20 = INDEX(INDIRECT($C$4),$D$2,3) 
S20 = INDEX(INDIRECT($C$4),$D$2,4) 

Challenge #21.xlsx (8.3 MB)

[IlgarZarbaliyev]

@BALBRECHT, it is good to use Indirect Function.

[anonymous43]

2023-06-09T18:11:00Z

Summary

Thank you for an interesting challenge, @IlgarZarbaliyev here is my solution using Index Match and If_else function.

Challenge #21.xlsx (8.3 MB)

[IlgarZarbaliyev]

Thank you for participating in the Excel Challenge related to Index Function! 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 in Excel.

Here is my solution to Workout #21!

Solution

• Reference: Sales: '=INDEX((I3:L6,O3:R6,I10:L13,O10:R13),D2,D3,D4)
• Sales Array: '=INDEX((I3:L6,O3:R6,I10:L13,O10:R13),D2,0,D4)

Challenge #21 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!

[QuadriAtharuOlayinka]

Challenge #21 _Quadri.xlsx (8.3 MB)

[Keith]

Hi @IlgarZarbaliyev,

Here is my solution to this workout.
Sales:

=INDEX((I3:L6,O3:R6,I10:L13,O10:R13),D2,D3,D4)

Quarterly:

=INDEX((I3:L6,O3:R6,I10:L13,O10:R13),D2,0,D4)

Please find attached file for this workout.

Excel Workout Challenge #21 Keith.xlsx (8.3 MB)

thanks
Keith

[Sujan_Chhetri]

@IlgarZarbaliyev - Here’s my submission

Sales (formula) - =INDEX(SWITCH($D$4,1,$I$3:$L$6,2,$O$3:$R$6,3,$I$10:$L$13,$O$10:$R$13,0),$D$2,$D$3)

Quarterly (formula) - =INDEX(SWITCH($D$4,1,$I$3:$L$6,2,$O$3:$R$6,3,$I$10:$L$13,4,O10:R13,0),$D$2,SWITCH(P$19,“North”,1,“South”,2,“East”,3,“West”,4,0))

Here’s my excel workout file - Challenge #21.xlsx (8.3 MB)