IF statement based on multiple conditions

[yorkam]

Can anyone assist?
I am trying to combine multiple conditions in a formula, it needs to be a formula and not in access.
I have a spreadsheet which simplistically contains three columns of data.
Col A - original date
Col B - payment date - will contain blanks until paid
Col C - delta between original and payment date, this is dynamic, formula in this cell is as follows. =if"B1="",(today()-A1),(B1-A1))

In column4 I want to put a formula that identifies for a range of values which bucket the data falls into based on formula in col 3, which is currently (Col1-col2)..

Formula I am using is
=if(C1<31,"Current",if(C1>=31<=60,"30",if(C1>=61<=90,"60",if(C1>91<=120,"90"if(c1>121<=150,"120","120+"))))
I can get the formula to work for first condition <31 but not for the conditions in between, result defaults to 120+

Anybody any ideas or tried this before of if there is a better formula to use?

Regards
A

 

[vds1]

Try,

=LOOKUP(SUMPRODUCT(--(C1>={0,31,61,91,121,151,152})),{1,2,3,4,5,6},{"Current","30","60","90","120","120+"})

 

[yorkam]

Many thanks for the quick response works perfectly, much obliged.

 

[Peter_SSs]

Ooops, wrong formula. I'll post again in a minute.

 

[Peter_SSs]

Welcome to the MrExcel board!

What about either of these simpler formulas?

Excel Workbook
	A	B	C	D	E
1	Original Date	Payment Date	Delta	Bucket	Bucket
2	1/01/14	15/01/14	14	Current	Current
3	1/01/14	27/03/14	85	60	60
4	1/01/14	1/03/14	59	30	30
5	1/01/14	2/03/14	60	30	30
6	1/01/14		162	120+	120+
7	1/01/14	28/05/14	147	120	120
8	1/01/14	3/04/14	92	90	90
Bucket