Grade using two range/conditions

[ukbulldog001]

Dear MrExcelits,

Need help in deriving Grade A to E using two sets of data.
Tried my best and stuck.
Please help in formula.
No macros please as I would share this file.

Example as below:

Grade Table to consider:

Book1
	B	C	D
2	GRADE	QUALITY	DELIVERY %
3	A	1-500	98-100
4	B	501-750	95-97
5	C	751-1000	92-94
6	D	1001-1300	90-91
7	E	>1300	85-89
Sheet1

Values to be considered for the Grade:

Book1
	F	G
3	QUALITY	636
4	DELIVERY	96
Sheet1

Result:

Book1
	I
3	GRADE
4	B
Sheet1

Cell Formulas
Range	Formula
I4	=IF(OR(G3<500,G4>=98),"A",IF(OR(G3<750,G3>500,G4>=95,G4<98),"B",IF(OR(G3<1000,G3>750,G4>=92,G4<95),"C",IF(OR(G3<1300,G3>1000,G4>=90,G4<92),"D",IF(OR(G3>1300,G4<85),"E","")))))

I have used the above formula but it doesn't work as expected.

Here if Quality=1101 and Delivery=96 then the Grade should be "D".
And if Quality=470 and Delivery=93 then the Grade should be "C".

Thanks in advance.

 

[jondavis1987]

So, it seems like whatever the lowest grade is between quality or delivery is what you want it to display, correct? Because in your first example the delivery amount would be grade A. The problem with your formula is you're using IF/OR. one of your two conditions is met for the grade A if you have 1101 quality and delivery of 96.

 

[jondavis1987]

=if(and(g3>=1,g3<=500,g4>=98),"a",if(and(g3>=1,g3<=500,g4>=95,g4<=97),"b",if(and(g3>=1,g3<=500,g4>=92,g4<=94),"c",if(and(g3>=1,g3<=500,g4>=90,g4<=91),"d",if(and(g3>=501,g3<=750,g4>=98),"b",if(and(g3>=751,g3<=1000,g4>=98),"c",if(and(g3>=1001,g3<=1300,g4>=98),"d",if(and(g3>=501,g3<=750,g4>=95,g4<=97),"b",if(and(g3>=501,g3<=750,g4>=92,g4<=94),"c",if(and(g3>=501,g3<=750,g4>=90,g4<=91),"c",if(and(g3>=751,g3<=1000,g4>=95,g4<=97),"c",if(and(g3>=1001,g3<=1300,g4>=95,g4<=97),"d",if(and(g3>=751,g3<=1000,g4>=92,g4<=94),"c",if(and(g3>=751,g3<=1000,g4>=90,g4<=91),"d",if(and(g3>=1001,g3<=1300,g4>=92,g4<=94),"d",if(and(g3>=1001,g3<=1300,g4>=90,g4<=91),"d","e"))))))))))))))))

 

[Fluff]

If you're happy modifying your table slightly, how about

Excel 2013/2016
	B	C	D	E	F	G	H	I
2	GRADE	QUALITY	DELIVERY %
3	A	1	100		QUALITY	470		GRADE
4	B	501	97		DELIVERY	93		C
5	C	751	94
6	D	1001	91
7	E	1301	89
Data

Cell Formulas
Range	Formula
I4	=INDEX(B3:B7,MAX(MATCH(G3,C3:C7,1),MATCH(G4,D3:D7,-1)))

 

[jondavis1987]

I figured the grade table was made just to help us understand with a visual. Also, i really need to look into the index formula.

 

[ukbulldog001]

=if(and(g3>=1,g3<=500,g4>=98),"a",if(and(g3>=1,g3<=500,g4>=95,g4<=97),"b",if(and(g3>=1,g3<=500,g4>=92,g4<=94),"c",if(and(g3>=1,g3<=500,g4>=90,g4<=91),"d",if(and(g3>=501,g3<=750,g4>=98),"b",if(and(g3>=751,g3<=1000,g4>=98),"c",if(and(g3>=1001,g3<=1300,g4>=98),"d",if(and(g3>=501,g3<=750,g4>=95,g4<=97),"b",if(and(g3>=501,g3<=750,g4>=92,g4<=94),"c",if(and(g3>=501,g3<=750,g4>=90,g4<=91),"c",if(and(g3>=751,g3<=1000,g4>=95,g4<=97),"c",if(and(g3>=1001,g3<=1300,g4>=95,g4<=97),"d",if(and(g3>=751,g3<=1000,g4>=92,g4<=94),"c",if(and(g3>=751,g3<=1000,g4>=90,g4<=91),"d",if(and(g3>=1001,g3<=1300,g4>=92,g4<=94),"d",if(and(g3>=1001,g3<=1300,g4>=90,g4<=91),"d","e"))))))))))))))))

Thanks @jondavis1987 works like a charm.

 

[ukbulldog001]

If you're happy modifying your table slightly, how about

Excel 2013/2016

	B	C	D	E	F	G	H	I
GRADE	QUALITY	DELIVERY %
A	QUALITY	GRADE
B	DELIVERY	C
C
D
E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[TD="align: center"][/TD]

[TD="align: right"]470[/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]501[/TD]
[TD="align: right"]97[/TD]
[TD="align: center"][/TD]

[TD="align: right"]93[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]751[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1001[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]1301[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Data​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I4[/TH]
[TD="align: left"]=INDEX(B3:B7,MAX(MATCH(G3,C3:C7,1),MATCH(G4,D3:D7,-1)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks @Fluff it works but table is used for visual ref only and I don't want to change the format of the file, since I'm sharing it to other people it would be hard for them to ref.

 

[Rick Rothstein]

Assuming the numbers in the table won't change, here is another formula that I think should work...

=CHAR(64+MAX(LOOKUP(G3,{1,501,751,1001,1301},{1,2,3,4,5}),LOOKUP(G4,{85,90,92,95,98},{5,4,3,2,1})))

 

[ukbulldog001]

Assuming the numbers in the table won't change, here is another formula that I think should work...

=CHAR(64+MAX(LOOKUP(G3,{1,501,751,1001,1301},{1,2,3,4,5}),LOOKUP(G4,{85,90,92,95,98},{5,4,3,2,1})))

This too works, thanks a lot @rick-rothstein