if multiple conditions

[Dexir]

Working.xlsx
	A	B	C	D	E	F	G	H
1
2	Town Status	Deal Slab	Total Sales	Eligibility
3	Secondary	Slab 1	12	Yes
4	Secondary	Slab 1	10	Yes
5	Secondary	Slab 3	29	Yes
6	Secondary	Slab 2	15	Yes		Secondary
7	Secondary	Slab 1	13	Yes		slab 1	10
8	Secondary	Slab 1	12	Yes		slab 2	15
9	Secondary	Slab 7	700	Yes		slab 3	25
10	Secondary	Slab 3	25	Yes		slab 4	50
11	Secondary	Slab 3	25	Yes		slab 5	100
12	Secondary	Slab 2	21	Yes		slab 6	200
13	Secondary	Slab 3	39	Yes		slab 7	700
14	Secondary	Slab 1	10	Yes
15	Secondary	Slab 1	14	Yes
16	Secondary	Slab 1	12	Yes
17
Sheet1

Cell Formulas
Range	Formula
D3:D16	D3	=IF(AND($A3=$F$6,$B3=$F$7,C3>=$G$7),"Yes",IF(AND($A3=$F$6,$B3=$F$9,C3>=$G$9),"Yes",IF(AND($A3=$F$6,$B3=$F$10,C3>=$G$10),"Yes",IF(AND($A3=$F$6,$B3=$F$11,C3>=$G$11),"Yes",IF(AND($A3=$F$6,$B3=$F$12,C3>=$G$12),"Yes",IF(AND($A3=$F$6,$B3=$F$13,C3>=$G$13),"Yes",IF(AND($A3=$F$6,$B3=$F$8,C3>=$G$8),"Yes","No")))))))

is there any easy formula for this sheet ?
Thanks in advance!

 

[Legacy 143009]

Hi,

This may work for you:

=IF(AND($A3=F$6,COUNTIFS($F$7:$F$13,$B3,$G$7:$G$13,"<="&$C3)>0),"Yes","No")

 

[Dexir]

Thank you Flashbond, it is working good. But i have some other conditions. would you please look into this, if there is an easy way.
Thanks.

Working.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	Town Status	Deal Slab	Total Sales	Eligibility	FOC Boxes
3	Primary	Slab 2	205	Yes	150
4	Secondary	Slab 2	10	No	0
5	Secondary	Slab 3	29	Yes	28
6	Secondary	Slab 2	12	No	0
7	Secondary	Slab 1	13	Yes	10
8	Primary	Slab 3	25	No	0
9	Secondary	Slab 3	25	Yes	28
10	Secondary	Slab 3	25	Yes	28
11	Secondary	Slab 2	21	Yes	16			Secondary		Primary
12	Primary	Slab 3	39	Yes	0		slab 1	10	10	100	60
13	Secondary	Slab 1	10	Yes	10		slab 2	15	16	200	150
14	Secondary	Slab 1	14	Yes	10		slab 3	25	28
15	Primary	Slab 1	12	No	0		slab 4	50	60
16	Secondary	Slab 1	14	Yes	10		slab 5	100	130
17	Secondary	Slab 1	10	Yes	10		slab 6	200	280
18	Secondary	Slab 2	16	Yes	16		slab 7	700	Bike	1056
19	Secondary	Slab 1	11	Yes	10
20
Sheet1

Cell Formulas
Range	Formula
E3:E19	E3	=IF($D3="NO",0,IF($C3<$H$12,0,IF(AND($B3=$G$12,$A3=$H$11,$C3>=$H$12),$I$12,IF(AND($B3=$G$13,$A3=$H$11,$C3>=$H$13),$I$13,IF(AND($B3=$G$14,$A3=$H$11,$C3>=$H$14),$I$14,IF(AND($B3=$G$15,$A3=$H$11,$C3>=$H$15),$I$15,IF(AND($B3=$G$16,$A3=$H$11,$C3>=$H$16),$I$16,IF(AND($B3=$G$17,$A3=$H$11,$C3>=$H$17),$I$17,IF(AND($B3=$G$18,$A3=$H$11,$C3>=$H$18),$J$18,IF(AND($B3=$G$12,$A3=$J$11,$C3>=$J$12),$K$12,IF(AND($B3=$G$13,$A3=$J$11,$C3>=$J$13),$K$13,0)))))))))))

 

[Legacy 143009]

Could you please explain why the 8th row is "No" and the 12th row is "Yes"?

 

[Dexir]

Could you please explain why the 8th row is "No" and the 12th row is "Yes"?

Pardon me, this is also "No" as per the conditions, actually the D column's formula is ok, that's why i didnt post it here.

 

[Legacy 143009]

D3:

=IF(AND(INDEX($H$12:$K$18,MATCH($B3,$G$12:$G$18,0),MATCH($A3,$H$11:$K$11,0))>0,INDEX($H$12:$K$18,MATCH($B3,$G$12:$G$18,0),MATCH($A3,$H$11:$K$11,0))<=$C3),"Yes","No")

E3:

=IF($D3="No",0,INDEX($H$12:$K$18,MATCH($B3,$G$12:$G$18,0),MATCH($A3,$H$11:$K$11,0)+1))

 

[Dexir]

Thank you so much! this is working perfectly.

 

[Legacy 143009]

You are welcome Thanks for the follow-up