# Assign the right cell/reference

#### zinah

##### Active Member
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
Excel Formula:
``````=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
ABCDEFG
1 Amount GrpLowHighGrp Number
2\$ 164,027.411\$ -\$ 19,239.001
3\$ 179,271.392\$ 19,240.00\$ 24,439.002
4\$ 870,166.693\$ 24,440.00\$ 30,679.003
5\$ 270,165.694\$ 30,680.00\$ 38,999.004
6\$ 211,265.985\$ 39,000.00\$ 49,919.005
7\$ 214,467.546\$ 49,920.00\$ 62,919.006
8\$ 200,647.897\$ 62,920.00\$ 80,079.007
9\$ 606,199.348\$ 80,080.00\$ 101,919.008
10\$ 241,331.069\$ 101,920.00\$ 128,959.009
11\$ 208,029.8210\$ 128,960.00\$ 163,799.0010
12\$ 161,087.8011\$ 163,800.00\$ 207,999.0011
13\$ 1,057,390.590\$ 208,000.00\$ 9,999,999.0012
14\$ 293,055.000
15\$ 417,233.070
16\$ 171,341.110
17\$ 275,793.790
18\$ 824,042.460
19\$ 185,205.780
20\$ 713,334.690
21\$ 224,214.210
22\$ 228,595.850
23\$ 153,980.810
24\$ 280,754.810
25\$ 177,777.220
26\$ 209,926.020
27\$ 229,542.030
28\$ 545,775.400
29\$ 145,956.840
30\$ 195,302.470
31\$ 165,367.780
32\$ 186,760.990
33\$ 179,238.330
34\$ 187,922.520
35\$ 205,553.560
36\$ 580,039.370
37\$ 236,481.180
38\$ 702,331.930
Sheet1
Cell Formulas
RangeFormula
B2:B38B2=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!

Last edited:

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Fluff

##### MrExcel MVP, Moderator
You have all the <= & >= the wrong way round, also the ORs should be AND, it should be like
Excel Formula:
``=IF(AND(A2>=E2,A2<=F2),G2,``

#### zinah

##### Active Member
You have all the <= & >= the wrong way round, also the ORs should be AND, it should be like
Excel Formula:
``=IF(AND(A2>=E2,A2<=F2),G2,``
Yes, I noticed that after I posted the question and I updated the formula. Thanks

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

#### jtakw

##### Well-known Member
Hi,

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

Book3.xlsx
ABCDEFG
1 Amount GrpLowHighGrp Number
2164027.4110192391
3179271.41119240244392
4870166.71224440306793
5270165.71230680389994
62112661239000499195
7214467.51249920629196
8200647.91162920800797
9606199.312800801019198
10241331.1121019201289599
11208029.81212896016379910
12161087.81016380020799911
13105739112208000999999912
1429305512
15417233.112
16171341.111
17275793.812
18824042.512
19185205.811
20713334.712
21224214.212
22228595.912
23153980.810
24280754.812
25177777.211
2620992612
2722954212
28545775.412
29145956.810
30195302.511
31165367.811
3218676111
33179238.311
34187922.511
35205553.611
36580039.412
37236481.212
38702331.912
Sheet904
Cell Formulas
RangeFormula
B2:B38B2=LOOKUP(A2,E\$2:E\$13,G\$2:G\$13)

#### zinah

##### Active Member
Hi,

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

Book3.xlsx
ABCDEFG
1 Amount GrpLowHighGrp Number
2164027.4110192391
3179271.41119240244392
4870166.71224440306793
5270165.71230680389994
62112661239000499195
7214467.51249920629196
8200647.91162920800797
9606199.312800801019198
10241331.1121019201289599
11208029.81212896016379910
12161087.81016380020799911
13105739112208000999999912
1429305512
15417233.112
16171341.111
17275793.812
18824042.512
19185205.811
20713334.712
21224214.212
22228595.912
23153980.810
24280754.812
25177777.211
2620992612
2722954212
28545775.412
29145956.810
30195302.511
31165367.811
3218676111
33179238.311
34187922.511
35205553.611
36580039.412
37236481.212
38702331.912
Sheet904
Cell Formulas
RangeFormula
B2:B38B2=LOOKUP(A2,E\$2:E\$13,G\$2:G\$13)
That worked perfectly great, thank you so much!

#### jtakw

##### Well-known Member
You're welcome, thanks for the feedback.

Replies
10
Views
263
Replies
5
Views
169
Replies
5
Views
150
Replies
6
Views
159
Replies
4
Views
117

1,181,068
Messages
5,927,919
Members
436,576
Latest member
rovman1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back