Multiple If Statements (Between)

[reneev]

I'm trying to make a formula that basically says if this number (4.57) is between B and C, then pull in A. Since there is more than 7 options, a regular IFTHAN statement won't work. I just can't think anymore and need help.

[TABLE="width: 178"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]0 - 6 mo[/TD]
[TD]0[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]6+ mo - 1 yr.[/TD]
[TD]0.6[/TD]
[TD]0.99[/TD]
[/TR]
[TR]
[TD]1 - 2 years[/TD]
[TD]1[/TD]
[TD]1.99[/TD]
[/TR]
[TR]
[TD]2 - 3 years[/TD]
[TD]2[/TD]
[TD]2.99[/TD]
[/TR]
[TR]
[TD]3 - 4 years[/TD]
[TD]3[/TD]
[TD]3.99[/TD]
[/TR]
[TR]
[TD]4 - 5 years[/TD]
[TD]4[/TD]
[TD]4.99[/TD]
[/TR]
[TR]
[TD]5 - 10 years [/TD]
[TD]5[/TD]
[TD]9.99[/TD]
[/TR]
[TR]
[TD]10 - 15 years [/TD]
[TD]10[/TD]
[TD]14.99[/TD]
[/TR]
[TR]
[TD]15 - 20 years[/TD]
[TD]15[/TD]
[TD]19.99[/TD]
[/TR]
[TR]
[TD]20+ years[/TD]
[TD]20[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]

 

[BAlGaInTl]

Can you store that table somewhere in your workbook so you can reference it? If so, you can do a simple Index/Match

=INDEX(A1:C10,MATCH(E1,B1:B10,1),1)

Where A1:C10 is the table, E1 is the value I was looking up.

ETA, you may want to name the ranges that you are looking up to make it easier to read:

=INDEX(LookupTable,MATCH(E1,LookupAge,1),1)

 

[reneev]

Thank you for your response. In my original post, I didn't realize that I wasn't explaining it right. The number is actually a column of varying numbers where each needs to be compared against the table and determined the year category. So if A1 is between B1 and C2 Or B2 and C2 (and so on) then return whatever is in column D.

 

[BAlGaInTl]

I think you are going to have to give a better example of what your actual data looks like, and what the expected result is.

 

[reneev]

Oh how's this:

I want the formula to look at the YOS and determine which service category it belongs in. The YOS field varies depending on the employee.

[TABLE="class: grid, width: 175"]
<tbody>[TR]
[TD="align: center"]Years of Service (YOS)[/TD]
[TD="align: center"]Service Category[/TD]
[/TR]
[TR]
[TD="align: center"].6[/TD]
[TD="align: center"]??[/TD]
[/TR]
[TR]
[TD="align: center"]5.2[/TD]
[TD="align: center"]??[/TD]
[/TR]
[TR]
[TD="align: center"]21.5[/TD]
[TD="align: center"]??[/TD]
[/TR]
</tbody>[/TABLE]
*The YOS cell is a formula based on hire date minus term date so it's not a round number.


Service Category options:

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD="align: center"]From[/TD]
[TD="align: center"]To[/TD]
[TD="align: center"]Service Category[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD].49[/TD]
[TD]0-6 months[/TD]
[/TR]
[TR]
[TD].5[/TD]
[TD].99[/TD]
[TD]6 mos - 1 year[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1.99[/TD]
[TD]1 - 2 years[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2.99[/TD]
[TD]2 - 3 years[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3.99[/TD]
[TD]3 - 4 years[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4.99[/TD]
[TD]4 - 5 years[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]9.99[/TD]
[TD]5 - 10 years[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]14.99[/TD]
[TD]10 - 15 years[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]19.99[/TD]
[TD]15 - 20 years[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]50[/TD]
[TD]20+ years[/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott Huish]

All you need for that is VLOOKUP.

Like this, change ranges as necessary.

Excel 2010
	A	B	C	D	E	F	G	H
1	Years of Service (YOS)	Service Category				From	To	Service Category
2	0.6	6 mos - 1 year				0	0.49	0-6 months
3	5.2	5 - 10 years				0.5	0.99	6 mos - 1 year
4	21.5	20+ years				1	1.99	1 - 2 years
5						2	2.99	2 - 3 years
6						3	3.99	3 - 4 years
7						4	4.99	4 - 5 years
8						5	9.99	5 - 10 years
9						10	14.99	10 - 15 years
10						15	19.99	15 - 20 years
11						20	50	20+ years
Sheet1

Cell Formulas
Range	Formula
B2	=IF(A2="","",VLOOKUP(A2,$F$2:$H$11,3,1))
B3	=IF(A3="","",VLOOKUP(A3,$F$2:$H$11,3,1))
B4	=IF(A4="","",VLOOKUP(A4,$F$2:$H$11,3,1))

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F
1	From	To	Service Category		Years of Service (YOS)	Service Category
2	0	0.49	0-6 months		0.6	6 mos - 1 year
3	0.5	0.99	6 mos - 1 year		5.2	5 - 10 years
4	1	1.99	1 - 2 years		21.5	20+ years
5	2	2.99	2 - 3 years
6	3	3.99	3 - 4 years
7	4	4.99	4 - 5 years
8	5	9.99	5 - 10 years
9	10	14.99	10 - 15 years
10	15	19.99	15 - 20 years
11	20	50	20+ years
Index

Cell Formulas
Range	Formula
F2	=INDEX($A$2:$C$11,MATCH(E2,$A$2:$A$11,1),3)

 

[dfsobral]

like this?

=INDEX(C1:C10,MATCH(YOS!A1,A1:A10,1))

c1:c10 - service category
a1:a10 - from (in service category)

 

[reneev]

YES!! That worked! Can you explain what the "","" means in this part of the formula IF(A2="",""

 

[Scott Huish]

If it's blank, return blank