Index Match error

[Dexir]

Greetings all! i have this sheet, i am trying to figure out this result with Index match, the (Red filled cell) for reference that what i want. with If condition its work, but too lengthy at all. please give a solution.
Thanks in advance!

Working2.xlsx
	A	B	C	D	E	F	G	H
1		Secondary	Primary
2	slab 1	10	100
3	slab 2	15	200
4	slab 3	25	300
5	slab 4	50	400
6	slab 5	100	500
7	slab 6	200	600
8	slab 7	700	700
9
10	Town Status	Deal Slab	Total Sales		Ok/Not Ok
11	Secondary	Slab 1	15		Ok		Not OK
12	Secondary	Slab 2	18
13	Secondary	Slab 3	28
14	Secondary	Slab 4	55
15	Secondary	Slab 5	106
16	Secondary	Slab 6	202
17	Secondary	Slab 2	15
18	Primary	Slab 1	101
19	Primary	Slab 2	201
20	Primary	Slab 3	301
21	Primary	Slab 4	401
22	Primary	Slab 5	501
23	Primary	Slab 6	601
24	Primary	Slab 7	701
25
Sheet1

Cell Formulas
Range	Formula
E11	E11	=IF(AND(INDEX($B$2:$C$8,MATCH($B11,$A$2:$A$8,0),MATCH($A11,$B$1:$C$1,0)>=$C11),(INDEX($B$2:$C$8,MATCH($B11,$A$2:$A$8,0),MATCH($A11,$B$1:$C$1,0))<=$B3)),"Ok","Not Ok")
G11	G11	=IF(A11=B$1,IF(AND(B11=A$2,$C11>=$B$2,C11<B$3),"OK","Not OK"))

 

[Fluff]

Looks like you have some of the brackets in the wrong place. Try

=IF(AND(INDEX($B$2:$C$8,MATCH($B11,$A$2:$A$8,0),MATCH($A11,$B$1:$C$1,0))>=$C11,INDEX($B$2:$C$8,MATCH($B11,$A$2:$A$8,0),MATCH($A11,$B$1:$C$1,0))<=$B3),"Ok","Not Ok")

 

[Dexir]

Thanks, but with this formula the one condition is correct.
=IF(AND($C11>=(INDEX($B$2:$C$8,MATCH($B11,$A$2:$A$8,0),MATCH($A11,$B$1:$C$1,0)))),"Ok","Not Ok")
i want to add other below mention condition too.
=IF($C11<=B3,"Ok","Not Ok")
and this is the criteria table. i cannt go out of this.

Working2.xlsx
	A	B	C
1		Secondary	Primary
2	slab 1	10	100
3	slab 2	15	200
4	slab 3	25	300
5	slab 4	50	400
6	slab 5	100	500
7	slab 6	200	600
8	slab 7	700	700
Sheet1

=IF(E17=AA$1,IF(AND(K17=AA$2,P17>=$AB$2,P17<AB$3),"OK",IF(AND(K17=AA$3,P17>=$AB$3,P17<AB$4),"OK",IF(AND(K17=AA$4,P17>=$AB$4,P17<AB$5),"OK",IF(AND(K17=AA$5,P17>=$AB$5,P17<AB$6),"OK",IF(AND(K17=AA$6,P17>=$AB$6,P17<AB$7),"OK",IF(AND(K17=AA$7,P17>=$AB$7,P17<AB$8),"OK",IF(AND(K17=AA$8,P17>=$AB$7,),"OK",IF(AND(E17=$AC$1,K17=$AC$2,P17>=$AD$2,P17<$AD$3),"OK",IF(AND(E17=$AC$1,K17=$AC$3,P17>=$AD$3),"OK","Not OK"))))))))))
this is the final formula, i hope you understand.

WS Hangama Sales Tracking Nationallys.xlsx
	AA	AB	AC	AD
1	Secondary	Secondary		PRIMARY
2	Slab 1	10	Slab 1	100
3	Slab 2	15	Slab 2	200
4	Slab 3	25
5	Slab 4	50
6	Slab 5	100
7	Slab 6	200
8	Slab 7	700
Shop Detail

 

[Fluff]

i hope you understand.

Fraid not, especially when that formula uses completely different cells.
Please explain (in words) what you are trying to do.

 

[Dexir]

=IF(AND(INDEX($B$2:$C$8,MATCH($B11,$A$2:$A$8,0),MATCH($A11,$B$1:$C$1,0))>=$C11,INDEX($B$2:$C$8,MATCH($B11,$A$2:$A$8,0),MATCH($A11,$B$1:$C$1,0))<=$B3),"Ok","Not Ok")
This formula working good sir!
but as well as i drag the formula down, the B3 change to B4 and so on.
i just want it to just fix in the criteria table.
it just stay between these:
Slab 1 = 10-14
Slab 2 = 15-24
Slab 3 = 25-49
so on. and primary too.
and Slab data is mixed, not in sequence as i post in below table.

Working2.xlsx
	A	B	C
1		Secondary	Primary
2	slab 1	10	100
3	slab 2	15	200
4	slab 3	25	300
5	slab 4	50	400
6	slab 5	100	500
7	slab 6	200	600
8	slab 7	700	700
Sheet1

 

[Fluff]

That does not explain what you are trying todo.

 

[Dexir]

I have criteria table like this:

Working2.xlsx
	A	B	C
1	Table No 1
2		Secondary	Primary
3	slab 1	10	100
4	slab 2	15	200
5	slab 3	25	300
6	slab 4	50	400
7	slab 5	100	500
8	slab 6	200	600
9	slab 7	700	700
Sheet1

and this is the data file:

Working2.xlsx
	A	B	C
11	Table No 2
12	Town Status	Deal Slab	Total Sales
13	Secondary	Slab 1	10
14	Secondary	Slab 5	17
15	Primary	Slab 4	23
16	Secondary	Slab 4	55
17	Primary	Slab 2	106
18	Secondary	Slab 3	199
19	Secondary	Slab 2	15
20	Primary	Slab 7	99
21	Secondary	Slab 2	201
22	Primary	Slab 2	301
23	Secondary	Slab 4	401
24	Primary	Slab 3	501
25	Primary	Slab 6	601
26	Secondary	Slab 1	701
Sheet1

Now what i want is that if C13 is less than Slab 1=10 than it should show "Not Ok", and if C13 is greater than Slab 2=15 than it show "Not Ok"
if stay between slab 1=10 and slab 2=15 than it show "Ok". i hope you understand..
sorry for my english

 

[Fluff]

What happens for Slab 7 as there is no Slab 8?

 

[Dexir]

There is no Slab 8 and Slab 7=700 or plus should show "Ok".

 

[Fluff]

Ok how about this for 2021

Fluff.xlsm
	A	B	C	D	E
1		Secondary	Primary
2	slab 1	10	100
3	slab 2	15	200
4	slab 3	25	300
5	slab 4	50	400
6	slab 5	100	500
7	slab 6	200	600
8	slab 7	700	700
9
10	Town Status	Deal Slab	Total Sales		Ok/Not Ok
11	Secondary	Slab 1	15		Not Ok
12	Secondary	Slab 2	18		OK
13	Secondary	Slab 3	28		OK
14	Secondary	Slab 4	55		OK
15	Secondary	Slab 5	106		OK
16	Secondary	Slab 6	202		OK
17	Secondary	Slab 2	15		OK
18	Primary	Slab 1	101		OK
19	Primary	Slab 2	201		OK
20	Primary	Slab 3	301		OK
21	Primary	Slab 4	401		OK
22	Primary	Slab 5	501		OK
23	Primary	Slab 6	601		OK
24	Primary	Slab 7	701		OK
Data

Cell Formulas
Range	Formula
E11:E24	E11	=LET(a,FILTER(FILTER($B$2:$C$8,$A$2:$A$8>=B11),$B$1:$C$1=A11),IF(AND(C11>=INDEX(a,1),C11<IFERROR(INDEX(a,2),10000)),"OK","Not Ok"))