Multiple if and statement

[DavosCH32]

I have tried without success to use multiple if and statements for the below, but nothing is working. I can get 1 if statement for 1 group (Days) but not sure how to add the rest. I need the cell to return the Code based on the Days (Full Time, 90 Days, 30 Days) and Number (1+, 2+ etc).
Index match would not work here as the number could contain decimals.
For the example below if they select Full Time and the number value is 7.5, it should return the code "Code 3". Any help would be greatly appreciated.

 

[iggydarsa]

Based on your screenshot assuming Days data starts from cell E2, Numbers data starts from F2, this formula goes on G2 and drag down:

=IF(E2="30 Days",IF(F2>=1,"Code 5",""),IF(E2="90 Days",IF(F2>=2.5,"Code 5",IF(F2>=2,"Code 4",IF(F2>=1,"Code 2",""))),IF(E2="Full Time",IF(F2>=10,"Code 10",IF(F2>=8,"Code 4",IF(F2>=7,"Code 3",IF(F2>=6,"Code 2",IF(F2>=4,"Code 1",""))))),"")))

 

[etaf]

how about
=INDEX($C$2:$C$10,MATCH(E2&F2,$A$2:$A$10&$B$2:$B$10,1))

or must it b a IF()

Book19
	A	B	C	D	E	F	G
1
2	30 days	1	code 5		full time	7.5	code 3
3	90 days	1	code 2		90 days	1.5	code 2
4	90 days	2	code 4
5	90 days	2.5	code5
6	full time	4	code 1
7	full time	6	code 2
8	full time	7	code 3
9	full time	8	code 4
10	full time	10	code 5
11
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=INDEX($C$2:$C$10,MATCH(E2&F2,$A$2:$A$10&$B$2:$B$10,1))

 

[DavosCH32]

Based on your screenshot assuming Days data starts from cell E2, Numbers data starts from F2, this formula goes on G2 and drag down:

=IF(E2="30 Days",IF(F2>=1,"Code 5",""),IF(E2="90 Days",IF(F2>=2.5,"Code 5",IF(F2>=2,"Code 4",IF(F2>=1,"Code 2",""))),IF(E2="Full Time",IF(F2>=10,"Code 10",IF(F2>=8,"Code 4",IF(F2>=7,"Code 3",IF(F2>=6,"Code 2",IF(F2>=4,"Code 1",""))))),"")))

Thank you iggydarsa!!!! This worked perfectly.

 

[xlhelp15]

how about
=INDEX($C$2:$C$10,MATCH(E2&F2,$A$2:$A$10&$B$2:$B$10,1))

or must it b a IF()

Book19
	A	B	C	D	E	F	G
1
2	30 days	1	code 5		full time	7.5	code 3
3	90 days	1	code 2		90 days	1.5	code 2
4	90 days	2	code 4
5	90 days	2.5	code5
6	full time	4	code 1
7	full time	6	code 2
8	full time	7	code 3
9	full time	8	code 4
10	full time	10	code 5
11
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=INDEX($C$2:$C$10,MATCH(E2&F2,$A$2:$A$10&$B$2:$B$10,1))

One of the easiest and quick resolution which i have seen... Kudos to you @etaf

 

[etaf]

you are welcome

 

[Peter_SSs]

One of the easiest and quick resolution which i have seen... Kudos to you @etaf

Hmm, I think that depends on a couple of things.

a) If the data is really as posted in post 1 where the numbers are followed by a "+" sign. If that is the case then the formula does not necessarily return the correct result. The OP stated that this result should be "code 3"

23 01 12.xlsm
	A	B	C	D	E	F	G
1
2	30 days	1+	code 5		full time	7.5	code 2
3	90 days	1+	code 2
4	90 days	2+	code 4
5	90 days	2.5+	code 5
6	full time	4+	code 1
7	full time	6+	code 2
8	full time	7+	code 3
9	full time	8+	code 4
10	full time	10+	code 5
Sample

Cell Formulas
Range	Formula
G2	G2	=INDEX($C$2:$C$10,MATCH(E2&F2,$A$2:$A$10&$B$2:$B$10,1))

b) If the "+" signs are not there, problems could still occur because of the different lengths of the numbers. For example, I believe this result should be "code 4"

23 01 12.xlsm
	A	B	C	D	E	F	G
1
2	30 days	1	code 5		full time	8.5	code 5
3	90 days	1	code 2
4	90 days	2	code 4
5	90 days	2.5	code 5
6	full time	4	code 1
7	full time	6	code 2
8	full time	7	code 3
9	full time	8	code 4
10	full time	10	code 5
Sample

Cell Formulas
Range	Formula
G2	G2	=INDEX($C$2:$C$10,MATCH(E2&F2,$A$2:$A$10&$B$2:$B$10,1))

The problem is that for MATCH to use "1" as its final argument the values have to be arranged in alphabetical order and in that case "full time10" does not come after "full time8". (In fact it would come before all the other full time entries in the sample data)

 

[etaf]

thanks for the error correction - Peter_SSs , i never knew that , i see now as adding the number makes it text and so the sort order changes - will keep that in mind for the future - Thanks again for taking the time to provide the correction

 

[Peter_SSs]

Thanks again for taking the time to provide the correction

No problem - though I didn't actually provide a correction, just pointed out the potential problems.

Given that the OP has stated that post #2 works perfectly, perhaps my comments about the "+" following the numbers may have been misguided in that the "+" may just be from some custom formatting rather than added text.

@DavosCH32
You might clarify the issue of the "+" signs just so we know.
But also, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, IF you have a version with the latest functions, and assuming column B is numerical, you could use this which again is much shorter than the post 2 suggestion.

23 01 12.xlsm
	A	B	C	D	E	F	G
1
2	30 days	1	Code 5		full time	7.5	Code 3
3	90 days	1	Code 2		90 days	1.5	Code 2
4	90 days	2	Code 4
5	90 days	2.5	Code 5
6	full time	4	Code 1
7	full time	6	Code 8
8	full time	7	Code 3
9	full time	8	Code 4
10	full time	10	Code 5
Code

Cell Formulas
Range	Formula
G2:G3	G2	=INDEX(SORT(FILTER(B$2:C$10,(A$2:A$10=E2)*(B$2:B$10<=F2)),1,-1),1,2)