If formula

[MrSupreme]

Hi All

Hopefully someone could help with my issue here. I am trying to create a formula which will return the correct value based on a certain criteria. Essentially, I have a list of areas (column D) and I want the formula in column C to fill in the correct unit type (column G) based on certain rules. The first if formula works fine and commands that any unit which is less than 538 should return a value of IB1P. However, when I add the second if formula which includes OR, the formula no longer works and returns an error. Would appreciate any help on this please.

 

[Joe4]

When you nest IF statements inside of one another, the you put the second IF within the TRUE or FALSE argument, not after it (that does nothing).
So I think your second IF should be imbedded where the first "" is, and then you need another ")" at the very end.

(I would've edited your formula for you, but you posted it as an image, and I cannot copy and edit an image!)

 

[AhoyNC]

See if one of these formulas works for you.
Formula in column B will return Unit less than or equal to Sq. Ft. in column H.
Formula in column C will return Unit that is less than Sq. Ft. in column H.

Book2
	A	B	C	D	E	F	G	H
1
2
3
4		Less than or = to H	Less than H	Area
5		IBIP	IBIP	487			Mimum Space Standards
6		2B3P	2B3P	663			Unit type	Sq. ft
7		2B3P	2B3P	672			IBIP	420
8		2B3P	2B3P	717			1B2P	538
9		2B3P	2B3P	677			2B3P	657
10		2B3P	2B3P	668			2 B4 p	753
11		IBIP	IBIP	447
12		IBIP	IBIP	452
13		2B3P	1B2P	657
14
Sheet1

Cell Formulas
Range	Formula
B5:B13	B5	=LOOKUP(D5,$H$7:$H$10,$G$7:$G$10)
C5:C13	C5	=LOOKUP(D5,$H$7:$H$10+1,$G$7:$G$10)

 

[craigg3]

Would this fix your current formula?
=IF(D5<$H$8,$G$7,IF(OR(D5>=$H$8,D5<$H$9),$G$8,""))

 

[MrSupreme]

When you nest IF statements inside of one another, the you put the second IF within the TRUE or FALSE argument, not after it (that does nothing).
So I think your second IF should be imbedded where the first "" is, and then you need another ")" at the very end.

(I would've edited your formula for you, but you posted it as an image, and I cannot copy and edit an image!)

First time using XL2BB function so hopefully you can now open in excel?

Book1
	B	C	D	E	F	G	H	I	J
2
3		Type	Area			Unit Type	Sq ft
4		1B1P	487			1B1P	420
5		1B1P	663			1B2P	538
6		1B1P	672			2B3P	657
7		1B1P	717
8		1B1P	677
9		1B1P	668
10		1B1P	447
11		1B1P	452
12		1B1P	253
13		1B1P	677
14		1B1P	668
15
16
Sheet1

Cell Formulas
Range	Formula
C4:C14	C4	=IF(D4<$H$5,$G$4,IF(OR(C4>=$H$5,C4<$H$6),$G$5,IF(C4>=$H$6,G6,"")))

 

[awoohaw]

is there a particular reason you cannot use INDEX/MATCH functionality (Or XLOOKUP - if you use 365).

Mr excel questions 63.xlsm
	A	B	C	D	E	F	G	H	I
1
2
3
4		Index/Match	Type	Area
5		1B1P	1B1P	487
6		2B3P	0	663			Unit Type	Sq ft
7		2B3P	0	672			1B1P	420
8		2B3P	0	717			1B2P	538
9		2B3P	0	677			2B3P	657
10		2B3P	0	668
11		1B1P	1B1P	447
12		1B1P	1B1P	452
13		Too Low	1B1P	253
14		2B3P	0	677
15		2B3P	0	668
MrSupreme

Cell Formulas
Range	Formula
B5:B15	B5	=IFERROR(INDEX($G$7:$G$9,MATCH(D5,$H$7:$H$9)),"Too Low")
C5	C5	=IF(D5<$H$8,$G$7,IF(OR(C5>=$H$8,C5<$H$9),$G$8,IF(C5>=$H$9,G9,"")))
C6:C8	C6	=IF(D6<$H$8,$G$7,IF(OR(C6>=$H$8,C6<$H$9),$G$8,IF(C6>=$H$9,#REF!,"")))
C9:C15	C9	=IF(D9<$H$8,$G$7,IF(OR(C9>=$H$8,C9<$H$9),$G$8,IF(C9>=$H$9,G10,"")))

 

[MrSupreme]

thank you all very much, the solution was ensuring the second if function is embedded with the preceding true or false argument

 

[Joe4]

You are welcome.
Glad we were able to help!