Workbook matching question

**bwoy** · 03-10-2010, 04:44 PM

Hi everybody I am David from Spain. I work in an educational academy and have been put in charge of a task that is beyond my woefully limited excel skills! Any hewlp would be REALLY appreciated.

Most of the work is done, but I need some help with the final part.

The (attached) speadsheet is to keep a record of students test results. There are 3 pages, the first (leveltest workbook) is unimportant as it is just for uploading users to our online platform. The second is the 'master' sheet where the office workers will be able to see the results. The third ('results') is imported from the online platform.

The Master sheet ALREADY imports the results and matches them to the correct user.

What I need is this:

I need the master sheet to look at the cells in columns G-N in the results sheet, and report back based on which cells are complete

For example if the cells in G,H,I and J have a value but L,M and N do not, I want it to display some text for J, as it is the highest complete (as in greater than 0) field. The text for every one would be a level (we use A1,A2,B1,B2,C1,C2,C2+ as our system of classifying our students).

**DonkeyOte** · 03-11-2010, 04:24 AM

David, you will find that if you provide expected results this will help people in understanding your requirements.

If I've understood...

First - to avoid repetitive calculations I would suggest the following:

Master!L2: 
=MATCH($K2;Results!$B:$B;0)
copied down to L201

Then - perhaps you're looking for:

Master!H2:
=IFERROR(INDEX(Results!$G$1:$N$1;MATCH(MAX(INDEX(Results!$G:$N;$L2;0));INDEX(Results!$G:$N;$L2;0);0));"No Score")
copied down to H201

the above will return the header associated with MAX score for a given individual across those 8 columns.

(pending your version you may need to translate the above functions into their Spanish equivalent - see the Translation link in my sig. if required)

**bwoy** · 03-11-2010, 06:07 AM

Thank you very much for taking the time to help. As you can see I'm finding my feet here!

Thanks again, I'll give it a try.

David

**teylyn** · 03-11-2010, 06:35 AM

while you're finding your feet, please note that it is not neccessary to quote the post that you are replying to. In fact, it just creates clutter.

Use the Quick Reply box below the last post, and if you need to quote from a previous post, restrict it to the pertinent lines.

thanks