nested if formula with AND or OR

[Cossie]

Hello
i would like some assistance with my Nested if statement please. my formula is:
=IF(OR(AND(DESIGNReport!$K6>'Cash Category Ranges '!$F$3:$F$10),AND(DESIGNReport!K6<='Cash Category Ranges '!$G$3:$G$10)),'Cash Category Ranges '!H3:H10,"X")
This returns correctly for the first category but incorrectly for any other condition. I would like it to look at DESIGNReport!$K6 and if it is between $50 k and $100 k return 2 and so on.
any assistance would be appreciated.
With thanks

-$ 300,000	$ 50,000	1​
$ 50,001	$ 100,000	2​
$ 100,001	$ 150,000	3​
$ 150,001	$ 200,000	4​
$ 200,001	$ 250,000	5​
$ 250,001	$ 300,000	6​
$ 300,001	$ 600,000	7​
$ 600,001	$ 2,000,000	8​

 

[StephenCrump]

Your formula construction is incorrect.

It will return the vector {1;2;3;4;5;6;7;8} (i.e. 'Cash Category Ranges '!H3:H10) if DESIGNReport!$K6 is greater than every value in 'Cash Category Ranges '!$F$3:$F$10,
OR if DESIGNReport!$K6 is less than or equal to every value in 'Cash Category Ranges '!$G$3:$G$10, i.e. if DESIGNReport!$K6 > $600,000.01 OR if DESIGNReport!$K6 <=$50,000.

In versions earlier than Excel 365, the result will display as 1, but if you evaluate the formula using F2 you'll see the result is actually {1;2;3;4;5;6;7;8}. So if DESIGNReport!$K6 is $20,000, say, the result displayed (1) will appear to be correct, but it is not really.

You could use VLOOKUP instead:

	A	B	C	D	E	F	G
1
2
3		300,000.00				-300,000.00	1
4		6				50,000.01	2
5						100,000.01	3
6						150,000.01	4
7						200,000.01	5
8						250,000.01	6
9						300,000.01	7
10						600,000.01	8
11						2,000,000.01	X
Cash Category Ranges

Cell Formulas
Range	Formula
B3	B3	=DESIGNreport!K6
B4	B4	=IFERROR(VLOOKUP(B3,F3:G11,2),"X")

 

[Cossie]

Thanks for the reply and solution