Concatenated VLookup comparison for conditional Formatting

Question

I'm looking to create a conditional formatting field which will compare the values of two cells on sheet 2, to then format the correct cell on sheet 1.

To give some background, I have a spreadsheet (sheet1) which gets information from (sheet2). (sheet1) concatenates the Assessment period (B4) with the Subject code (AA12) and the Pupil ID (A15) which in turn is used to look up the corresponding field in sheet 2 to get the information.

=VLOOKUP(CONCATENATE($B$4,AA$12,$A15),sheet2!$F:$M,7,FALSE)

Here, Column F in (sheet2) holds the concatenated field names and column L holds the Grade, I.e A,B,C etc. Column M in (sheet2) holds the numeric equivalent of that grade, i.e A = 1, B = 2 etc.

The rows in (sheet2) hold information on the target grade and the current performed grade, as picked up from the concat where the assessment period is either 'Target Grades' OR 'Autumn End of Term'.

What I am looking to do is to have the grade in my original cell in (sheet1) to have conditional formatting applied based on if the numeric equivalent of the end of term grades in (sheet2) is <, = or > the target grades.

Im not sure if this is possible and have found some other helpful posts such as this one; Multi-column vlookup conditional formatting

But my issue is that I have 000's of records so would be very time consuming and data heavy to have a conditional format for each and every record such as proposed;

=IF(sheet2!M12<ODBC!M4,"RED",IF(sheet2!M12=ODBC!M4,"YELLOW",IF(sheet2!M12>ODBC!M4,"GREEN")))

What is needed is to incorporate the VLookup so that this can be calculated all at once and basically smash these two functions together;

=AA15 < vlookup(CONCATENATE($B$4,AA$12,$A15)ODBC!$F:$M,8,FALSE)

=(ODBC!$F:M,8,FALSE)<(ODBC!$F:M,8,FALSE)

Please see the below images FYR

Sheet1;

Sheet2;

I'm not even sure if this is possible and is well beyond the scope of my Excel experience so any help would be very appreciated!

EDIT

I have tried this Formulae for the Green colour;

=VLOOKUP(CONCATENATE($A$2,E$5,$A8),Sheet2!$F:M,8,FALSE)<VLOOKUP(CONCATENATE($B$4,G$5,$A8),Sheet2!$F:M,8,FALSE)

This works but for some reason, when applied to other sections with the same added but with a '>' or '=' for red or orange it seems to break and not format properly.

Any Ideas?

Maybe I got it wrong, but for all I see in your question you already have all the material you need, just put it together. If I didn't, please post a Sample.XLS that covers your needs that we can help you better. — Diogo Paim
Thanks for the quick response @DiogoPaim, I believe I have most of the what I need but I cant figure out a way in which to put it all together. Ill post a sample asap. Thanks — David Morgan
Here is the link to file dropper, this was the easiest online file share i could find :) filedropper.com/example_6 — David Morgan

David Morgan David Morgan · Accepted Answer · 2016-02-09T09:26:48

I Have fixed this! After much looking around it seems as though I was on the right track with the EDITs formulae;

=VLOOKUP(CONCATENATE($A$2,E$12,$A1),ODBC!$F:$M,8,FALSE)>VLOOKUP(CONCATENATE($A$4,E$12,$A1),ODBC!$F:$M,8,FALSE)

After coming back to this after a few days, the formulae seemed to work and I was able to successfully spread across the whole sheet, saving me countless hours of individual formatting! Not sure why this was not working originally, it may have had something to do with my cell selection within the "Use a formula to decide which cells to format"!

Concatenated VLookup comparison for conditional Formatting

1 Answers