# IF & Then statement

#### Fahsid1230

##### Active Member
Folks, Good Morning
here is the logic:
<3 years =176
3 - 5 yeras = 192
5 - 7 years = 208
7 -10 years = 224
>10 years = 240

This is all comparing to colum which has 1 -10 (in years).
So folks, If I need to try the first logic (<3 years =176) I can write a statemen like this =if(E2<3, 176) but I dont know how to use the IF & Then statement for 3 - 5 years for a cap for 192 and so forth. Remember E is the colum to which we are comparing with.
Any help?
thx

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### ExcelChampion

##### Well-known Member
You could use a LOOKUP formula to this and it would be easier.
Book1
ABCDE
1LookupTableLookupvalueFormula
201769224
33192
45208
57224
610240
Sheet1

If you don't want to create a lookup table you could hard code the values into the formula as in:

=VLOOKUP(D2,{0,176;3,192;5,208;7,224;10,240},2)

#### dave3009

##### Well-known Member
=IF(B18<=3,176,IF(AND(B18>3,B18<=5),192,IF(AND(B18>5,B18<=7),208,IF(AND(B18>7,B18<=10),224,IF(B18>10,240,)))))

Where B18 is your reference cell.

Kind Regards

Dave

#### lenze

##### Legend
A simple version

=IF(B18<=3,176,IF(B18<=5,192,IF(B18<=7,208,IF(B18<=10,224,240))))

Note that the ANDs are not necessary.

lenze

BTW: Todd's Lookup solution is quite viable and might be the best way to go depending on how large your dataset is. VLOOKUP will probably execute faster than the nested IFs.

thx so much.

Replies
3
Views
658
Replies
3
Views
196
Replies
0
Views
413
Replies
11
Views
813
Replies
5
Views
259

1,181,056
Messages
5,927,864
Members
436,573
Latest member
CMR237

### 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