Assign the right cell/reference

[zinah]

Hi,

I have below table and what I need is to assign the right grp number when the condition is met.
The current formula is

=IF(OR(A2<=E2,A2>=F2),G2,
IF(OR(A2<=E3,A2>F3),G3,
IF(OR(A2<=E4,A2>=F4),G4,
IF(OR(A2<=E5,A2>=F5),G5,
IF(OR(A2<=E6,A2>=F6),G6,
IF(OR(A2<=E7,A2>=F7),G7,
IF(OR(A2<=E8,A2>=F8),G8,
IF(OR(A2<=E9,A2>=F9),G9,
IF(OR(A2<=E10,A2>=F10),G10,
IF(OR(A2<=E11,A2>=F11),G11,
IF(OR(A2<=E12,A2>=F12),G12,
IF(OR(A2<=E13,A2>=F13),G13))))))))))))

But this formula is not working as it's expected, is there any alternative formula that works for my situation?

Book1
	A	B	C	D	E	F	G
1	Amount	Grp			Low	High	Grp Number
2	$ 164,027.41	1			$ -	$ 19,239.00	1
3	$ 179,271.39	2			$ 19,240.00	$ 24,439.00	2
4	$ 870,166.69	3			$ 24,440.00	$ 30,679.00	3
5	$ 270,165.69	4			$ 30,680.00	$ 38,999.00	4
6	$ 211,265.98	5			$ 39,000.00	$ 49,919.00	5
7	$ 214,467.54	6			$ 49,920.00	$ 62,919.00	6
8	$ 200,647.89	7			$ 62,920.00	$ 80,079.00	7
9	$ 606,199.34	8			$ 80,080.00	$ 101,919.00	8
10	$ 241,331.06	9			$ 101,920.00	$ 128,959.00	9
11	$ 208,029.82	10			$ 128,960.00	$ 163,799.00	10
12	$ 161,087.80	11			$ 163,800.00	$ 207,999.00	11
13	$ 1,057,390.59	0			$ 208,000.00	$ 9,999,999.00	12
14	$ 293,055.00	0
15	$ 417,233.07	0
16	$ 171,341.11	0
17	$ 275,793.79	0
18	$ 824,042.46	0
19	$ 185,205.78	0
20	$ 713,334.69	0
21	$ 224,214.21	0
22	$ 228,595.85	0
23	$ 153,980.81	0
24	$ 280,754.81	0
25	$ 177,777.22	0
26	$ 209,926.02	0
27	$ 229,542.03	0
28	$ 545,775.40	0
29	$ 145,956.84	0
30	$ 195,302.47	0
31	$ 165,367.78	0
32	$ 186,760.99	0
33	$ 179,238.33	0
34	$ 187,922.52	0
35	$ 205,553.56	0
36	$ 580,039.37	0
37	$ 236,481.18	0
38	$ 702,331.93	0
Sheet1

Cell Formulas
Range	Formula
B2:B38	B2	=IF(OR(A2<=E2,A2>=F2),G2, IF(OR(A2<=E3,A2>F3),G3, IF(OR(A2<=E4,A2>=F4),G4, IF(OR(A2<=E5,A2>=F5),G5, IF(OR(A2<=E6,A2>=F6),G6, IF(OR(A2<=E7,A2>=F7),G7, IF(OR(A2<=E8,A2>=F8),G8, IF(OR(A2<=E9,A2>=F9),G9, IF(OR(A2<=E10,A2>=F10),G10, IF(OR(A2<=E11,A2>=F11),G11, IF(OR(A2<=E12,A2>=F12),G12, IF(OR(A2<=E13,A2>=F13),G13))))))))))))

Thanks!

 

[Fluff]

You have all the <= & >= the wrong way round, also the ORs should be AND, it should be like

=IF(AND(A2>=E2,A2<=F2),G2,

 

[zinah]

You have all the <= & >= the wrong way round, also the ORs should be AND, it should be like

=IF(AND(A2>=E2,A2<=F2),G2,

Yes, I noticed that after I posted the question and I updated the formula. Thanks

 

[Fluff]

You're welcome & thanks for the feedback.

 

[jtakw]

Hi,

Since you Already have a Table, why not make good use of it and simplify your formula to this:

Book3.xlsx
	A	B	C	D	E	F	G
1	Amount	Grp			Low	High	Grp Number
2	164027.4	11			0	19239	1
3	179271.4	11			19240	24439	2
4	870166.7	12			24440	30679	3
5	270165.7	12			30680	38999	4
6	211266	12			39000	49919	5
7	214467.5	12			49920	62919	6
8	200647.9	11			62920	80079	7
9	606199.3	12			80080	101919	8
10	241331.1	12			101920	128959	9
11	208029.8	12			128960	163799	10
12	161087.8	10			163800	207999	11
13	1057391	12			208000	9999999	12
14	293055	12
15	417233.1	12
16	171341.1	11
17	275793.8	12
18	824042.5	12
19	185205.8	11
20	713334.7	12
21	224214.2	12
22	228595.9	12
23	153980.8	10
24	280754.8	12
25	177777.2	11
26	209926	12
27	229542	12
28	545775.4	12
29	145956.8	10
30	195302.5	11
31	165367.8	11
32	186761	11
33	179238.3	11
34	187922.5	11
35	205553.6	11
36	580039.4	12
37	236481.2	12
38	702331.9	12
Sheet904

Cell Formulas
Range	Formula
B2:B38	B2	=LOOKUP(A2,E$2:E$13,G$2:G$13)

 

[zinah]

Hi,

Since you Already have a Table, why not make good use of it and simplify your formula to this:

Book3.xlsx
	A	B	C	D	E	F	G
1	Amount	Grp			Low	High	Grp Number
2	164027.4	11			0	19239	1
3	179271.4	11			19240	24439	2
4	870166.7	12			24440	30679	3
5	270165.7	12			30680	38999	4
6	211266	12			39000	49919	5
7	214467.5	12			49920	62919	6
8	200647.9	11			62920	80079	7
9	606199.3	12			80080	101919	8
10	241331.1	12			101920	128959	9
11	208029.8	12			128960	163799	10
12	161087.8	10			163800	207999	11
13	1057391	12			208000	9999999	12
14	293055	12
15	417233.1	12
16	171341.1	11
17	275793.8	12
18	824042.5	12
19	185205.8	11
20	713334.7	12
21	224214.2	12
22	228595.9	12
23	153980.8	10
24	280754.8	12
25	177777.2	11
26	209926	12
27	229542	12
28	545775.4	12
29	145956.8	10
30	195302.5	11
31	165367.8	11
32	186761	11
33	179238.3	11
34	187922.5	11
35	205553.6	11
36	580039.4	12
37	236481.2	12
38	702331.9	12
Sheet904

Cell Formulas
Range	Formula
B2:B38	B2	=LOOKUP(A2,E$2:E$13,G$2:G$13)

That worked perfectly great, thank you so much!

 

[jtakw]

You're welcome, thanks for the feedback.