Multiple IF/VLOOKUP String (Excel Example Attached)

D

DigitalData

New Member
Joined
Aug 25, 2017
Messages
21
All,

I want to thank you in advance for helping me. I just need help creating a custom IF/VLOOKUP string based off of a list table. I will attach the file for an example. It should be pretty straightforward. Once I get past 3 or so IFs combined it starts getting choppy for me. I really do appreciate the help and people taking their own time to help others on here.

Column A = Hire Date
Column B = Term Date
Column C = Employment Length (formula for Term Date - Hire Date)
Column D = MIN DAYS
Column E = MAX DAYS
Column F = Criteria needs to fit

So I need one formula for column to see to check if it fits ANY of the criteras from column 5. Which one is it basically.

[TABLE="width: 1168"]
<tbody>[TR]
[TD]Hire Date[/TD]
[TD]Term Date[/TD]
[TD]Employment Length[/TD]
[TD]MINIMUM DAYS[/TD]
[TD]MAXIMUM DAYS[/TD]
[TD]TENURE TYPE[/TD]
[/TR]
[TR]
[TD]6/1/2018[/TD]
[TD]6/2/2018[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]2.99[/TD]
[TD]1) 0-2 Days[/TD]
[/TR]
[TR]
[TD]5/30/2018[/TD]
[TD]6/2/2018[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]7.99[/TD]
[TD]2) 3-7 Days[/TD]
[/TR]
[TR]
[TD]5/15/2018[/TD]
[TD]6/2/2018[/TD]
[TD]18[/TD]
[TD]8[/TD]
[TD]30[/TD]
[TD]3) 8-30 Days[/TD]
[/TR]
[TR]
[TD]5/1/2018[/TD]
[TD]6/2/2018[/TD]
[TD]32[/TD]
[TD]30.01[/TD]
[TD]60[/TD]
[TD]4) 30-60 Days[/TD]
[/TR]
[TR]
[TD]4/1/2018[/TD]
[TD]6/2/2018[/TD]
[TD]62[/TD]
[TD]60.01[/TD]
[TD]90[/TD]
[TD]5) 60-90 Days[/TD]
[/TR]
[TR]
[TD]3/1/2018[/TD]
[TD]6/2/2018[/TD]
[TD]93[/TD]
[TD]90.01[/TD]
[TD]180[/TD]
[TD]6) 90-180 Days[/TD]
[/TR]
[TR]
[TD]1/1/2017[/TD]
[TD]6/2/2018[/TD]
[TD]517[/TD]
[TD]180[/TD]
[TD]999999999[/TD]
[TD]7) Greater Than 180 Days[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]REAL EXAMPLE[/TD]
[/TR]
[TR]
[TD]Employee Number[/TD]
[TD]Name[/TD]
[TD]Hire Date[/TD]
[TD]Term Date[/TD]
[TD]Employment Length[/TD]
[TD]Tenure Type[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Mr Excel Is Amazing[/TD]
[TD]5/1/2018[/TD]
[TD]6/2/2018[/TD]
[TD]32[/TD]
[TD] =IF(AND(E14>D2,E14 ETC ETC ETC ETC .. Check for all criterias in above table lol<e2=f2 etc="" etc<="" td=""></e2=f2>[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
The file supplied by the OP on EF is like this


Excel 2013 32 bit
ABCDEF
1MINIMUM DAYSMAXIMUM DAYSTENURE TYPE
202.991) 0-2 Days
337.992) 3-7 Days
48303) 8-30 Days
530.01604) 30-60 Days
660.01905) 60-90 Days
790.011806) 90-180 Days
81809999999997) Greater Than 180 Days
9
10
11
12REAL EXAMPLE
13Employee NumberNameHire DateTerm DateEmployment LengthTenure Type
14123Mr Excel Is Amazing01/05/201802/06/201832=IF(AND(A2>B2=C2,=IF(A2
Sheet23
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
The only reason this is working is because the example has 32 and the above table has 32. Change the term date to 6/6/18 or something. It appears to work, but only because it can validate 32 in the other table I think.




Marcelo Branco said:
No, no, you don't need many IFs. If the values in D2:D8 are in ascending order (as your data sample above) LOOKUP works! See below


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Hire Date​
[/TD]
[TD]
Term Date​
[/TD]
[TD]
Employment Length​
[/TD]
[TD]
MINIMUM DAYS​
[/TD]
[TD]
MAXIMUM DAYS​
[/TD]
[TD]
TENURE TYPE​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
06/01/2018​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
1​
[/TD]
[TD]
0​
[/TD]
[TD]
2,99​
[/TD]
[TD]
1) 0-2 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
05/30/2018​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
3​
[/TD]
[TD]
3​
[/TD]
[TD]
7,99​
[/TD]
[TD]
2) 3-7 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
05/15/2018​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
18​
[/TD]
[TD]
8​
[/TD]
[TD]
30​
[/TD]
[TD]
3) 8-30 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
05/01/2018​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
32​
[/TD]
[TD]
30,01​
[/TD]
[TD]
60​
[/TD]
[TD]
4) 30-60 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
04/01/2018​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
62​
[/TD]
[TD]
60,01​
[/TD]
[TD]
90​
[/TD]
[TD]
5) 60-90 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
03/01/2018​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
93​
[/TD]
[TD]
90,01​
[/TD]
[TD]
180​
[/TD]
[TD]
6) 90-180 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
01/01/2017​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
517​
[/TD]
[TD]
180,01​
[/TD]
[TD]
999999999​
[/TD]
[TD]
7) Greater Than 180 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13​
[/TD]
[TD]
Employee Number​
[/TD]
[TD]
Name​
[/TD]
[TD]
Hire Date​
[/TD]
[TD]
Term Date​
[/TD]
[TD]
Employment Length​
[/TD]
[TD]
Tenure Type​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14​
[/TD]
[TD]
123​
[/TD]
[TD]
Mr Excel Is Amazing​
[/TD]
[TD]
05/01/2018​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
32​
[/TD]
[TD]
4) 30-60 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in F14
=LOOKUP(E14,D$2:D$8,F$2:F$8)

M.
Click to expand...
 
Upvote 0
By the way, the values (tenures) in column F should be - adjustments in red


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
F​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
TENURE TYPE​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
1) 0-2 Days​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
2) 3-7 Days​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
3) 8-30 Days​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
4) 31-60 Days​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
5) 61-90 Days​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
6) 91-180 Days​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
7) Greater Than 180 Days​
[/td][/tr]
[/table]

M.
 
Upvote 0
Think about what a lookup is doing there is no way to get a correct value back from using a lookup in a 7 scenario. You MAY be able to use INDEX MATCH somehow, but that would be a intense formula. The best I've seen and use is IFERROR + 2 VLOOKUPS.
 
Upvote 0
No they are already correct they are not my choice. Aka why I had min and max values set to match them.

Marcelo Branco said:
By the way, the values (tenures) in column F should be - adjustments in red


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
TENURE TYPE​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
1) 0-2 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
2) 3-7 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
3) 8-30 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
4) 31-60 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
5) 61-90 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
6) 91-180 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
7) Greater Than 180 Days​
[/TD]
[/TR]
</tbody>[/TABLE]


M.
Click to expand...
 
Upvote 0
DigitalData said:
The only reason this is working is because the example has 32 and the above table has 32. Change the term date to 6/6/18 or something. It appears to work, but only because it can validate 32 in the other table I think.
Click to expand...

It works, believe me!
Have you tried with different values in E14 - example changing the the Hire Date in C14 (in red)


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Hire Date​
[/TD]
[TD]
Term Date​
[/TD]
[TD]
Employment Length​
[/TD]
[TD]
MINIMUM DAYS​
[/TD]
[TD]
MAXIMUM DAYS​
[/TD]
[TD]
TENURE TYPE​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
06/01/2018​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
1​
[/TD]
[TD]
0​
[/TD]
[TD]
2,99​
[/TD]
[TD]
1) 0-2 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
05/30/2018​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
3​
[/TD]
[TD]
3​
[/TD]
[TD]
7,99​
[/TD]
[TD]
2) 3-7 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
05/15/2018​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
18​
[/TD]
[TD]
8​
[/TD]
[TD]
30​
[/TD]
[TD]
3) 8-30 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
05/01/2018​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
32​
[/TD]
[TD]
30,01​
[/TD]
[TD]
60​
[/TD]
[TD]
4) 31-60 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
04/01/2018​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
62​
[/TD]
[TD]
60,01​
[/TD]
[TD]
90​
[/TD]
[TD]
5) 61-90 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
03/01/2018​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
93​
[/TD]
[TD]
90,01​
[/TD]
[TD]
180​
[/TD]
[TD]
6) 91-180 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
01/01/2017​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
517​
[/TD]
[TD]
180,01​
[/TD]
[TD]
999999999​
[/TD]
[TD]
7) Greater Than 180 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13​
[/TD]
[TD]
Employee Number​
[/TD]
[TD]
Name​
[/TD]
[TD]
Hire Date​
[/TD]
[TD]
Term Date​
[/TD]
[TD]
Employment Length​
[/TD]
[TD]
Tenure Type​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14​
[/TD]
[TD]
123​
[/TD]
[TD]
Mr Excel Is Amazing​
[/TD]
[TD]
05/01/2017​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
397​
[/TD]
[TD]
7) Greater Than 180 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


M.
 
Upvote 0
Doesnt make any sense it would work past this one little table IMO. Can you attach the sheet?




Marcelo Branco said:
It works, believe me!
Have you tried with different values in E14 - example changing the the Hire Date in C14 (in red)


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Hire Date​
[/TD]
[TD]
Term Date​
[/TD]
[TD]
Employment Length​
[/TD]
[TD]
MINIMUM DAYS​
[/TD]
[TD]
MAXIMUM DAYS​
[/TD]
[TD]
TENURE TYPE​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
06/01/2018​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
1​
[/TD]
[TD]
0​
[/TD]
[TD]
2,99​
[/TD]
[TD]
1) 0-2 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
05/30/2018​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
3​
[/TD]
[TD]
3​
[/TD]
[TD]
7,99​
[/TD]
[TD]
2) 3-7 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
05/15/2018​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
18​
[/TD]
[TD]
8​
[/TD]
[TD]
30​
[/TD]
[TD]
3) 8-30 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
05/01/2018​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
32​
[/TD]
[TD]
30,01​
[/TD]
[TD]
60​
[/TD]
[TD]
4) 31-60 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
04/01/2018​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
62​
[/TD]
[TD]
60,01​
[/TD]
[TD]
90​
[/TD]
[TD]
5) 61-90 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
03/01/2018​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
93​
[/TD]
[TD]
90,01​
[/TD]
[TD]
180​
[/TD]
[TD]
6) 91-180 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
01/01/2017​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
517​
[/TD]
[TD]
180,01​
[/TD]
[TD]
999999999​
[/TD]
[TD]
7) Greater Than 180 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13​
[/TD]
[TD]
Employee Number​
[/TD]
[TD]
Name​
[/TD]
[TD]
Hire Date​
[/TD]
[TD]
Term Date​
[/TD]
[TD]
Employment Length​
[/TD]
[TD]
Tenure Type​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14​
[/TD]
[TD]
123​
[/TD]
[TD]
Mr Excel Is Amazing​
[/TD]
[TD]
05/01/2017​
[/TD]
[TD]
06/02/2018​
[/TD]
[TD]
397​
[/TD]
[TD]
7) Greater Than 180 Days​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


M.
Click to expand...
 
Upvote 0
Using the data sample kindly provided by Fluff in post 11 (with the necessary adjustments)

Dates as dd/mm/yyyy

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
MINIMUM DAYS​
[/td][td]
MAXIMUM DAYS​
[/td][td]
TENURE TYPE​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
0​
[/td][td]
2,99​
[/td][td]
1) 0-2 Days​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
3​
[/td][td]
7,99​
[/td][td]
2) 3-7 Days​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
8​
[/td][td]
30​
[/td][td]
3) 8-30 Days​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
30,01​
[/td][td]
60​
[/td][td]
4) 31-60 Days​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
60,01​
[/td][td]
90​
[/td][td]
5) 61-90 Days​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
90,01​
[/td][td]
180​
[/td][td]
6) 91-180 Days​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
180,01​
[/td][td]
999999999​
[/td][td]
7) Greater Than 180 Days​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
12​
[/td][td]
REAL EXAMPLE​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
13​
[/td][td]
Employee Number​
[/td][td]
Name​
[/td][td]
Hire Date​
[/td][td]
Term Date​
[/td][td]
Employment Length​
[/td][td]
Tenure Type​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
14​
[/td][td]
123​
[/td][td]
Mr Excel Is Amazing​
[/td][td]
01/05/2018​
[/td][td]
02/06/2018​
[/td][td]
32​
[/td][td]
4) 31-60 Days​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
15​
[/td][td]
124​
[/td][td]
Employee 2​
[/td][td]
01/01/2018​
[/td][td]
02/06/2018​
[/td][td]
152​
[/td][td]
6) 91-180 Days​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
16​
[/td][td]
125​
[/td][td]
Employee 3​
[/td][td]
20/05/2018​
[/td][td]
02/06/2018​
[/td][td]
13​
[/td][td]
3) 8-30 Days​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
17​
[/td][td]
126​
[/td][td]
Employee 4​
[/td][td]
02/01/2017​
[/td][td]
02/06/2018​
[/td][td]
516​
[/td][td]
7) Greater Than 180 Days​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
18​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in E14 copied down
=D14-C14

Formula in F14 copied down
=LOOKUP(E14,A$2:A$8,C$2:C$8)

M.
 
Upvote 0
DigitalData said:
No they are already correct they are not my choice. Aka why I had min and max values set to match them.
Click to expand...


It does not make sense to have a track
3) 8-30 Days
and another track
4) 30-60 Days
One must decide whether 30 Days is in track 3 or in track 4. What implicitly was done by placing 30.01 on A5 which indicates that only lengths greater than 30 belong to track 4.

M.
 
Upvote 0
DigitalData said:
Think about what a lookup is doing there is no way to get a correct value back from using a lookup in a 7 scenario. You MAY be able to use INDEX MATCH somehow, but that would be a intense formula. The best I've seen and use is IFERROR + 2 VLOOKUPS.
Click to expand...

You also can use INDEX/MATCH, but it would be a simple formula, not a complex (intense) one ;)
Using the data sample in post 18 try
F14 copied down
=INDEX(C$2:C$8,MATCH(E14,A$2:A$8))

M.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Analytics Project - Sumifs
Replies
1
Views
512
bobaol
B
M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
538
mrshuck
M
L
  • Question Question
INDEX MATCH
Replies
1
Views
322
Fluff
Fluff
K
  • Solved
help with formatting a cell (for TIME, custom format) that retrieves its value from another cell...
Replies
2
Views
341
kbishop94
K
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
236
etaf
etaf

Forum statistics

Threads
1,222,830
Messages
6,168,511
Members
452,194
Latest member
Lowie27

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top