Matrix Building using VLOOKUP, INDEX and MATCH

Hi,

I'm trying to generate a matrix kind of structure which I want to use for display in a dashboard the input for this dashboard is taken from an automated report taken from an external system.

I could not post the original data, so I've tried to provide a mocked up data sheet, which I've enclosed in this thread.

The data taken from an external system is of a specific format in a specified order. I've mocked it in the Source sheet. I'm trying to build a matrix with different combinations subject-wise (A, B, C, and so on etc.) and use it in the output sheet, using VLOOKUP, INDEX and MATCH combination. But I'm getting baffled while building the matrix due to the different combinations.

Can someone help me build the matrix and help me with the coding the VLOOKUP, INDEX and MATCH formulas in my output sheet as well?

Do let me know if you need any further information.

Here's some additional information :

The data from the external system comes out in different sheets each time (I've copied and pasted it into the Source worksheet). This source consists of Users (User1, User2, User3, etc.), Subjects (A, B, C, etc.), Scores (Combo1, Combo2, Combo3, etc.) and their corresponding Grades (Combo1A, Combo2A, Combo3A, etc.). This data is for different terms (5 in total) and each collection would have scores and grades pertaining to all users for all subjects pertaining to a particular term. There are n users and x subjects in total.

I'm looking at having multiple types of outputs :

a. User-wise listing of scores & grades for all terms subject-wise (layout as in Output sheet)
b. Subject-wise grades and scores for all users (layout as in Matrix sheet)

Bumping due to no response..

Sorry, can you explain a bit clearer?

Which part you want me to explain again? Did you have a look at the attachment? I've mocked it with random numbers and alphabets, but in my actual data there will be scores in place of the numbers and grades in place of alphabets.

So in Output, shouldn't Combo1 User 1 "A" be 4 not 2 (from source B3)?

Also, when a column doesn't have a letter, as in the first source table column G, does that mean it's the same as the letter in the previous column (i.e D)?

It can be achieved easily if you restructure your data

Instead of keeping the data as each regions/areas keep the data in running format by allocating column headers in the top only

Agreed on both points from ChemistB and SixthSense: the output is not clear and the data layout leaves a lot to be desired.

Regards

I've remodelled the Source sheet and updated it with comments. As I said earlier, I need two types of Output - Subject-wise and User-wise.

Originally Posted by ChemistB

So in Output, shouldn't Combo1 User 1 "A" be 4 not 2 (from source B3)?

Have you noticed that he is using Randbetween to populate the sample values, so every time the sheet is opened, the values are recalculated.

I'm not worried about the random values populated as this is just sample data. I only need to get the scores and grades in the cells in my output tables, say by using VLOOKUP with and / or INDEX and MATCH functions.

Kindly have a look at my remodelled worksheet in my post #9.

In reality, will the headers actually be Combo1, Combo2, etc or will they just be Score and Grade, with the only identification being which table they come from?

Originally Posted by ChemistB

In reality, will the headers actually be Combo1, Combo2, etc or will they just be Score and Grade, with the only identification being which table they come from?

As mentioned in post #2, in the Source sheet, Combo1 would be score1 for all subjects (A, B, C, etc.) and grade for all Key stage results relevant to the first term (the first table), similarly in the second table, it will be Combo2 would be score2 and grade for all Key stage results for all subjects pertaining to 2nd term, and so on. Likewise, there will be 5 tables for a full year extracted into 5 different sheets, each pertaining to a particular term. This data is an extract of the MIS report executed and exported from an external system from time to time.

Actually, in my original data sheet, Combo1, Combo2, etc. are called Pred1, Pred2, etc. (predictive scores) and grade is called KSR (Key Stage results). The data for the Source sheet that I've uploaded here contains data till the completed term.

Enclosed herewith is the screenshot of the actually extract. I've mocked out the names, but retained the subject titles. There will be x number of users and y subjects.

I find it difficult to locate and fill in the predictive grades and Key stage results in the output formats that I need i.e. subject-wise and user-wise.

Definitions :
Pred1, Pred2, Pred3 etc are the Predicted grades for each Student in that particular year and the 1, 2, 3 refers to the term - These WOULD change as each term progresses as the students SHOULD improve as the year goes on.

KS is the Key Stage results for each Learner - These will be the same every term (By saying same, I do not mean the values or results, but its an indicator whether the student has Passed or Failed.

Try this to see if you get the desired output.
On Output Matrix sheet......

In B3

Please Login or Register  to view this content.

and then drag across and down.

In B15

Please Login or Register  to view this content.

and then drag across and down.

Hi sktneer,

Your solution is working fine in my model workbook, however, when I try to convert them to my original data, its giving me errors.
Refer attached workbook.

If the formula is working fine with the workbook you attached, you only need to make sure that the formula is referencing the correct range in your original workbook.
Evaluate the formula to see if the correct range is being referenced or not?

I've checked it and its not working, please refer the attachment in my post #15.

Perhaps you should add some of your manually-calculated desired results to this version of the file.

Regards

Enclosed the sheet which contains the expected output from my input data. The main problem I face is that there are merged cells in my source sheet while trying to incorporate the solution given by sktneer.

There's only one tab in there (?)

Regards

Originally Posted by XOR LX

There's only one tab in there (?)

Regards

That's the output I want to generate for my input workbook available in my post #15.

Ah, with separate workbooks we won't have easy resource to e.g. 3D referencing across tabs (which may well form part of the solution, unless you think otherwise), so I feel that this may be something I can't help you with.

All the best and regards.

Originally Posted by XOR LX

Ah, with separate workbooks we won't have easy resource to e.g. 3D referencing across tabs (which may well form part of the solution, unless you think otherwise), so I feel that this may be something I can't help you with.

All the best and regards.

Apologies XOR, I've enclosed the workbook which I'm working with. I want to incorporate the formulas in the Summary sheet to get my results as in the Expected_Output worksheet. Can u help me?

Sorry - it's too poor a set-up. You have merged cells in all your headers and, worse, some of the subjects span two merged cells whilst others, e.g. Btec Sport apply to only a single column.

Excel can do wonderful things. But that's no reason to say we shouldn't always first make sure that our layout and structure are designed in the best possible manner so as to facilitate analysis/reporting, etc., not to mention the formula work.

In short, I think you need to have a re-think about the design of your workbook.

Regards

Originally Posted by XOR LX

Sorry - it's too poor a set-up. You have merged cells in all your headers and, worse, some of the subjects span two merged cells whilst others, e.g. Btec Sport apply to only a single column.

In short, I think you need to have a re-think about the design of your workbook.

Regards

Thanks for the clarifications. I received these extracts from an external system from support team. I'll ask them to provide me the data with correct format. I'm marking this thread as solved and will start a fresh one.

Find the attached sheet to see if this works as per your need.

Thanks sktneer.. I'll check out the formula to understand how it works and will let you know if I have any questions.
Its exactly what I need, but if the cells are blank in the source sheets, I would need it be blank in the Output sheet as well. Currently, it reflects as zero.

See the attached sheet.