Conditional formatting and keywords displayed based on two different cells

[ntech]

Excel 2011 32 bit

	B	C	D

<tbody>
[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]Program[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]Score[/TD]
[TD="align: center"]Grade[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]RM1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]3[/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]CRA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]5[/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]CRB1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]18[/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]CRB2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]13[/TD]
[TD="align: center"][/TD]

</tbody>

Conditional Formating​

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 13.9px Calibri; color: #000000}</style>I would like to set up conditional formatting to include the keywords and color for cells D3:D7 based on the program code and criteria below. If that is too complicated, I would settle for just the key word.

Excel 2011 32 bit

	B	C	D	E	F

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]Conditional Formatting Criteria for RM1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EFEFEF]#EFEFEF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]Conditional Formatting Criteria for CRA[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00FF00]#00FF00[/URL] , align: center"]Excellent[/TD]
[TD="align: center"]19 or more[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EFEFEF]#EFEFEF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00FF00]#00FF00[/URL] , align: center"]Excellent[/TD]
[TD="align: center"]18 or more[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=6AA84F]#6AA84F[/URL] , align: center"]Good[/TD]
[TD="align: center"]16-18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EFEFEF]#EFEFEF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=6AA84F]#6AA84F[/URL] , align: center"]Good[/TD]
[TD="align: center"]15-17[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Average[/TD]
[TD="align: center"]13-15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EFEFEF]#EFEFEF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Average[/TD]
[TD="align: center"]12-14[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF9900]#FF9900[/URL] , align: center"]Needs Improvement[/TD]
[TD="align: center"]9-12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EFEFEF]#EFEFEF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF9900]#FF9900[/URL] , align: center"]Needs Improvement[/TD]
[TD="align: center"]9-11[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]Unsatisfactory[/TD]
[TD="align: center"]0-8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EFEFEF]#EFEFEF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]Unsatisfactory[/TD]
[TD="align: center"]0-8[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EFEFEF]#EFEFEF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EFEFEF]#EFEFEF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EFEFEF]#EFEFEF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EFEFEF]#EFEFEF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EFEFEF]#EFEFEF[/URL] , align: center"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]Conditional Formatting Criteria for CRB1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EFEFEF]#EFEFEF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]Conditional Formatting Criteria for CRB2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00FF00]#00FF00[/URL] , align: center"]Excellent[/TD]
[TD="align: center"]16 or more[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EFEFEF]#EFEFEF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00FF00]#00FF00[/URL] , align: center"]Excellent[/TD]
[TD="align: center"]11 or more[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=6AA84F]#6AA84F[/URL] , align: center"]Good[/TD]
[TD="align: center"]13-15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EFEFEF]#EFEFEF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=6AA84F]#6AA84F[/URL] , align: center"]Good[/TD]
[TD="align: center"]9-10[/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Average[/TD]
[TD="align: center"]11-12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EFEFEF]#EFEFEF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Average[/TD]
[TD="align: center"]7-8[/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF9900]#FF9900[/URL] , align: center"]Needs Improvement[/TD]
[TD="align: center"]8-10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EFEFEF]#EFEFEF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF9900]#FF9900[/URL] , align: center"]Needs Improvement[/TD]
[TD="align: center"]5-6[/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]Unsatisfactory[/TD]
[TD="align: center"]0-7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EFEFEF]#EFEFEF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]Unsatisfactory[/TD]
[TD="align: center"]0-4[/TD]

</tbody>

Conditional Formating​

 

[etaf]

can you layout the lookup tables in a different format

you can look up the value from a simple table

 

[etaf]

Sheet2

	A	B	C	D	E	F	G	H	I

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:137px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:198px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: center"]RM1[/TD]
[TD="align: center"]CRA[/TD]
[TD="align: center"]CRB1[/TD]
[TD="align: center"]CRB2[/TD]

[TD="align: center"]Program[/TD]
[TD="align: center"]Scrore[/TD]
[TD="align: center"]Grade[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]unsatisfactory[/TD]

[TD="align: center"]RM1[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]Average[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Needs Improvement[/TD]

[TD="align: center"]CRA[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]Average[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]Average[/TD]

[TD="align: center"]CRA[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]Needs Improvement[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]Good[/TD]

[TD="align: center"]CRB1[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]Average[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]Excellent[/TD]

[TD="align: center"]CRB1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]unsatisfactory[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: center"]CRB2[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]Average[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula I2 =INDEX($E$2:$E$6,MATCH(H2,INDIRECT(ADDRESS(2,MATCH(G2,$A$1:$D$1,0))&":"&ADDRESS(6,MATCH(G2,$A$1:$D$1,0))),1)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4