In an effort to organize staff training: So far I've made the pivot table, but I'd like for the pivot table to extract who needs which training, as a list in one cell. I tried using =JOIN, but I have no idea what else.

On sheet 1:

  • Row 2: Staff (who need the training), They enter "NEED" in the row of the column for that training.
  • Column D: The Names of Trainings
  • Column E: Trainers/Staff who are providing the training Sheet 1 Data

On Pivot Table


  • A: Trainer
  • B: What modules they're teaching
  • C: How long the training is
  • D: A cell that lists who requested the training (who entered "NEED" into Sheet 1)

I'm trying to tell it simply:

  • Look for the name of THIS training over in sheet 1, AND if the value of a cell in that row is "NEED", Return the Name of person/people and list it in one cell next to this pivot table......(lollll I will not cry).

The formula would be something like:

  • Pull the name of the people from Sheet1!F1:Z if (Training names) Sheet1!D:D match Pivot table B:B AND there's a "NEED" in the row of that training....then list those names in ONE CELL.

  • I'm over explaining I think, but this has taken all day and my brain simply cannot any longer and my own formulas sheet is not helping, Please help if you can.

Pivot Table

I have tried: =JOIN mixed with Match, then Filter... Idk what i'm doing. And the pivot table has no option to filter "NEEDS" from the whole data set. Instead it makes me filter the "NEEDS" of a Name/Column, but if i do that, it will filter out everyone elses data. Basically it forces me to make a custom formula, which is great because I can learn, but... I really hope all of this made sense :(. Any help would be heavenly.


1 Answer 1


Your data table looks nice and pretty — i.e., it is like a report.

The best practice is to keep your data row-oriented and simple, and create reports separately — do not try to make your data look like a report.

You may want to use a row-oriented data layout, like this:

Name             Subject     Quarter   Score
Marie Curie       Chemistry   Q1       100
Albert Einstein   Chemistry   Q1       34
Marie Curie       Math       Q1       88
Albert Einstein   Math       Q1       66

This loosely corresponds with second normal form.

The primary benefit of a row-oriented data layout that it makes it easy to use spreadsheet functions like query() and filter() to further process the data and create reports.

Have you considered creating a form instead of sharing a spreadsheet for data input? Form responses automatically appear in a separate tab in the spreadsheet in a row-oriented fashion and are thus easy to process.

  • Thanks! Also if I were to lay it out like that, how would I allow them to be able to select multiple pieces of training? I could see a matrix in a form, but it would cause the same parsing/filter issues I'm currently having It seems.
    – D W
    Commented Jul 23, 2021 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.