Conditional formatting and keywords displayed based on two different cells

[ntech]

Excel 2011 32 bit

	B	C	D
2	Program	Score	Grade
3	RM1	3
4	CRA	5
5	CRB1	18
6	CRB2	13

<tbody>
</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
8	Conditional Formatting Criteria for RM1			Conditional Formatting Criteria for CRA
9	Excellent	19 or more		Excellent	18 or more
10	Good	16-18		Good	15-17
11	Average	13-15		Average	12-14
12	Needs Improvement	9-12		Needs Improvement	9-11
13	Unsatisfactory	0-8		Unsatisfactory	0-8
14
15	Conditional Formatting Criteria for CRB1			Conditional Formatting Criteria for CRB2
16	Excellent	16 or more		Excellent	11 or more
17	Good	13-15		Good	9-10
18	Average	11-12		Average	7-8
19	Needs Improvement	8-10		Needs Improvement	5-6
20	Unsatisfactory	0-7		Unsatisfactory	0-4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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
1	RM1	CRA	CRB1	CRB2			Program	Scrore	Grade
2	0	0	0	0	unsatisfactory		RM1	13	Average
3	9	9	8	5	Needs Improvement		CRA	12	Average
4	13	12	11	7	Average		CRA	11	Needs Improvement
5	16	15	13	9	Good		CRB1	11	Average
6	19	18	16	11	Excellent		CRB1	4	unsatisfactory
7							CRB2	7	Average

<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>
</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