Excel Tables by Value

mail-mergemicrosoft excelworksheet-function

I have a spreadsheet that includes student name, parent name, class and t-shirt size, with one row for each student. I would like to draw from this to create a new table, where each class is a row, and the names of all of the students in the class and their emergency contacts are columns. I would also like another worksheet where the shirt sizes are rows, and each student is a column. My end goal is to use mail merge to generate a list of students for each teacher and a list of who should get each size t-shirt.
It seems like VLookup should be able to do it, or maybe there is a way with a pivot table.

Here is an example input

Here is an example input

and outputs:

outputs

Can someone help me figure out how to get Excel to generate that information? Thanks in advance!

Best Answer

This Array Formula will solve the issue:

enter image description here

How it works:

  • Formula in Cell G88 to get Table by T- Shirt:

    {=IFERROR(INDEX($A$88:$A$92,SMALL(IF($B$88:$B$92=$F88,ROW($A$88:$A$92)-MIN(ROW($A$88:$A$92))+1),COLUMNS($F$88:F88))),"")}

N.B.

  • Finish the Formula with Ctrl+Shift+Enter.
  • Fill Formula Down the Right.

  • Formula in Cell G94 to get Table by Name & Contact Number:

    =IFERROR(INDEX($A$88:$A$92,SMALL(IF($C$88:$C$92=$F94,ROW($A$88:$A$92)-MIN(ROW($A$88:$A$92))+1),COLUMNS($F$94:F94)))," ")&" "& IFERROR(INDEX($D$88:$D$92,SMALL(IF($C$88:$C$92=$F94,ROW($D$88:$D$92)-MIN(ROW($D$88:$D$92))+1),COLUMNS($F$94:F94)))," ")

N.B.

  • To avoid writing the Formula twice (Since Names are in Col A and Contact numbers in col D), once for Name and for Contact Number, I've Concatenated it, therefore Formula returns Name along with Contact Number in same Cell.
  • Finish the Formula with Ctrl+Shift+Enter.
  • Fill Formula Down the Right.
Related Question