CODEWITHSUNDEEP
arraysexcelexcel-formulacross-reference
Jake Lee
Jake Lee

Reputation: 45

Trouble using array functions in Excel to create summary table

I have a table with a list of teachers' names in a column, and the columns that follow it are individual training courses. The idea is for each course we'd have one "Lead Tutor" and three other "Tutors", and these labels are allocated against selected teachers' names. So it looks something like:

Teacher training1 training2 training3
john Lead tutor
bob Tutor Tutor Tutor
jane Tutor Tutor
alice Tutor Lead tutor
tim Tutor Tutor Tutor
gus Lead tutor

What I am trying to create a separate summary table to show who has been assigned to each course, something like:

Lead tutor Tutor Tutor Tutor
training1 john bob alice tim
training2 alice bob jane tim
training3 gus bob jane tim

I know I need something like index/match/small/if/column/row but of all the examples I've found online I just can't get it to work...

The closest I got it to work is:

{=IFERROR(INDEX($A$2:$A$14,SMALL(IF(G$2=$B$2:$C$14,ROW($B$2:$C$14)-1,"not allocated"),COLUMNS($F$3:F3))),"")}

It doesn't give me error messages but it also didn't give me the correct results...!

I've uploaded my attempt on GoogleDrive - would be immensely grateful if someone could shed some light on what I did wrong (I've only learnt about arrays and index/aggregate/match on youtube but just couldn't get my head round it...!)

https://drive.google.com/file/d/1YKhKppAevNGrU_XFGe3IVNFOXynoKFiS/view?usp=sharing

Thanks in advance

Upvotes: 0

Views: 80

Answers (2)

Variatus
Variatus

Reputation: 14383

I chose a 2-formula approach. It would be so much nicer to have a single formula and just copy it to the entire table but I judged that the effort wasn't worth the effect. Therefore I have the following formula to extract the lead tutor. (183)

=INDEX(Table1[Teacher],MATCH(B$11,INDIRECT("Table1["&$A12&"]"),0))

Toward this end I converted your first table to an Excel table (Table1) covering the range A1:D7. It's an exact copy of your table. Therefore the headers are "Teacher", "Training1", "Training2" and "Training3". Therefore Table1[Teacher] is equivalent to A2:A7. You might substitute one syntax for the other but using a table will give you a dynamic range which you will other wise have to create.

I have your output table in range A11:E14. Therefore I have "Training1", "Training2" and "Training3" in A12:A14. So, INDIRECT("Table1["&$A12&"]" refers to the table columns of these names as the formula is copied down. This component features prominently in the formula that follows.

The above formula is in B12 and is then copied to B13:14. Observe its reference to B11, where it gets "lead tutor" from. Change its reference to A12 to catch the row where you have "Training1", which must match the column caption in the table.

Here is the formula for cell C12. You can use it as it is in Office 365 but it would be an array formula in earlier versions of Excel. Array formulas require confirmation by CTL + SHIFT + ENTER. After adjusting it, copy it to C14:E14.

=INDEX(Table1[Teacher],SMALL(IF(INDIRECT("Table1["&$A12&"]")="Tutor",ROW(INDIRECT("Table1["&$A12&"]"))-ROW(INDEX(INDIRECT("Table1["&$A12&"]"),1))+1),COLUMN()-2))

There is an irregularity in this formula which I decided to leave for you to fix. As you see, it contains the word "Tutor" (as is different from "Lead tutor") to search for in the table. In my test table I have the word in C11:E11 (copied from your table design) and should have taken it from there, as I did in the first formula which refers to B11. As it is, it will help you understand the formula but you can replace the word "Tutor" with C$11 if that's the way you set up your table.

There is a reference to the table's "Teacher" column and 3 references to A12, already explained above. I draw your attention to COLUMN()-2). When you enter this formula in column C it will return 1, and copied to the right it will count 2 and 3. This is how the rank for the SMALL function is defined. Adjust it depending upon the column to which you paste the formula.

Edit

Assuming you don't want to convert the first table into a table you might reference the ranges by names that you assign (and make dynamic in that process). Create 4 named ranges as follows:-

A2:A7 as "Teachers"
B2:B7 as "Training1"
C2:C7 as "Training2"
D2:D7 as "Training3"

Observe that 3 of the newly named ranges have their names in A12:A14. Now the formulas for B12 and C12 would look as follows.

[B12] =INDEX(Teachers,MATCH(B$11,INDIRECT($A12),0))
[C12] = INDEX(Teachers,SMALL(IF(INDEX(INDIRECT($A12),,1)="Tutor",ROW(INDEX(INDIRECT($A12),,1))-ROW(INDEX(INDIRECT($A12),1))+1),COLUMN()-2))

Upvotes: 1

P.b
P.b

Reputation: 11578

I worked on a single formula to use.

In G3 use the following: =LET(x,INDEX(Table24,,MATCH($F3,$1:$1,0)),IFERROR(INDEX(Table24[[name]:[name]],SMALL(IF(G$2=x,ROW(x)-1,""),COUNTIF($G$2:G$2,G$2))),""))

Or if you have an older version of Excel use this (avoiding LET function):

=IFERROR(INDEX(Table24[[name]:[name]],SMALL(IF(G$2=INDEX(Table24,,MATCH($F3,$1:$1,0)),ROW(INDEX(Table24,,MATCH($F3,$1:$1,0)))-1,""),COUNTIF($G$2:G$2,G$2))),"")

Upvotes: 1

Related Questions