Multiple IF/VLOOKUP String (Excel Example Attached)

[DigitalData]

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]

 

[Marcelo Branco]

Maybe...

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

M.

 

[DigitalData]

Thanks for the reply! That will not work for this many variables. Most like would be IF/AND then a ton of greater/less thans to fill each scenario out I would think.

 

[Marcelo Branco]

Thanks for the reply! That will not work for this many variables. Most like would be IF/AND then a ton of greater/less thans to fill each scenario out I would think.

The formula in post 2 works perfectly for the data you have showed above. If you have a different scenario show a data sample and maybe we could help.

M.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...if-vlookup-string-excel-example-attached.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[DigitalData]

The formula in post 2 works perfectly for the data you have showed above. If you have a different scenario show a data sample and maybe we could help.

M.

The employment length could be anything so it would have to fit any of the above list data scenarios. If Employment is 10 days then it would be 3) 8-30 Days.. The example is fine..

 

[DigitalData]

The formula in post 2 works perfectly for the data you have showed above. If you have a different scenario show a data sample and maybe we could help.

M.

Forget the hire date term date at the top that is all is just to help understand the only thing that matters is min days/max days and tenure type. I need employment date (whatever it may be) to always fit one of the 7 scenarios. In other works it will end up probably being 7 nested IF scenarios

 

[Fluff]

Not sure if Marcelo is a member of EF or not. But from the test file you supplied there all you need to do is modify Marcelo' formula to
=LOOKUP(E14,A$2:A$8,C$2:C$8)
& it does what you asked for.

 

[DigitalData]

Not sure if Marcelo is a member of EF or not. But from the test file you supplied there all you need to do is modify Marcelo' formula to
=LOOKUP(E14,A$2:A$8,C$2:C$8)
& it does what you asked for.

column A shouldnt even be in the formula so I know it cant be right

 

[Marcelo Branco]

Forget the hire date term date at the top that is all is just to help understand the only thing that matters is min days/max days and tenure type. I need employment date (whatever it may be) to always fit one of the 7 scenarios. In other works it will end up probably being 7 nested IF scenarios

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.