May 11, 2020

How to pull data where column heading matches AND row heading matches - Report card generator

I have been asked to create a report card generator. I need to be able to pull data from another sheet where the column heading match and the Row heading match.

Aim: I am trying to create a report card generator that will pull data from google form response sheets (Class1, Class2). I want to be able to select pupils using the two drop down menus at the top (C2 and F2) and the Pupil Viewer sheet then pull the relevant pupil's data (Effort, Comment and Teacher) for each subject from the correct sheet. If data is missing I want it to display "NO DATA".

Example sheet

I can get this to work using Vlookup but it falls down as there are multiple teachers of each subject and Vlookup only gives the first match.

I would greatly appreciate any help you can give me!

Many thanks in advance!

John

Details

Sheets,Microsoft Windows,Education

Locked

Informational notification.

This question is locked and replying has been disabled.

Community content may not be verified or up-to-date. Learn more.

All Replies (3)

Naoki O.

May 12, 2020

Hi John,

You can still use vlookup for this.

1. Create new sheet tab(eg; sample)

1. Use transpose (Class1!C6:R16) in sample sheet to cover all data.

2. It generates student's name for 2 different rows.

3. You can use vlookup for both Effort and Comment in Pupils Viewer tab.
eg, SQ teacher: VLOOKUP(F2,sample!B:K,9,false) for Efforts /Vlookup(F2,sample!A:K,10,false) for Comments

3. You can also use if() for the output of "No data"

Does this help for your report card?

Naoki

Tranquility Eden

Original Poster

May 12, 2020

Hello,

I don't think I can use vlookup. When I transpose it I will still have multiple columns which have the English heading. The formula won't know which column to look for the data in. The order the subjects appear and frequency would be random as they rely on google forms entries.

Thanks for you help!

JOhn

Ben Liebrand

Diamond Product Expert

Docs Editor, Google Sheets

May 12, 2020

Hi, John,

I had a look at your spreadsheet last night and there is no solution for it as you have it now.

The reason being, functions and formulas can not guess what the result should be.

You will need to redesign your data structure. Let me explain why I am saying this. In your example, you show that Jason has no effort value for Spanish. In this case, there is only one Spanish teacher. So you can display that teacher and output **no data**. There is no other Spanish teacher choice so that is a lucky guess.

But let's say, for argument sake, Jason had no effort value for Numeracy. There are 3 Numeracy teachers. Which techer should be chosen for **no data**?

You can see the problem. You cannot really have a data structure that works on guesses.

The only way you could make a project like this work is to have relationships between the pupil, teacher, subject and class.

So, this is the reason I am pointing out that you will need to put more thought into the relationship of the data structure.

If you feel I have this wrong then please explain what I am missing?

It is like going to the bank to make a deposit and the bank has a list of bank account numbers and a list of customers but there is no link between the bank account numbers and customers. How would the bank teller know which bank account number to choose? As you know, what makes it even trickier is that there could be more that one caustomer with the same name.

I hope this makes sense.

Ben Liebrand