How to Create a Formula Based on Multiple Conditions

[bperry1792]

Hello,

I apologize as I am probably using the incorrect terms to say this but I'm going to try the best I can. I have a spreadsheet that currently looks like this:

What I need to do is to have the end date auto-populate based on the Job Level and the Department Phase. For example, if the Job Level is Level 1, and the Department Phase shows "Design Revisions 1" and the average lead time for those revisions is a week (workdays), then I want to be able to put the Start Date in and have the end date automatically pop up 5 days after the start date. 

I currently have 4 different Job Levels, 7 different Current Departments, and 51 different Department Phases (but those are broken up by Department) and I'd need to put a certain length of time for each of those. Does anyone have any suggestions or formulas that I can use? 

Thanks in advance!

[#2]

@bperry1792 

Pictures are good, but an actual spreadsheet--so long as it doesn't contain any proprietary information--would be many times better. Otherwise you're asking us to copy yours (and possibly, therefore, be missing some things that are off-screen)......

I think your request is clear enough, but it would just be a lot easier to help you through seeing and working with the spreadsheet that you depict in that image.

[#3]

@mathetes Thanks for your reply! Attached is a smaller version of my spreadsheet (the original was too big to attach) - let me know if I can provide anything else. 

[#4]

@bperry1792  so I tweaked your reference sheet to have Phase and Level making a table.  Fill in the estimated times into that table then you can use that table to do a lookup to fill in the estimated end time:

If those aren't "hard"/known times but you have a large file of past jobs and such you could do the same thing but fill in the table by using formula (probably a couple sumifs()/countif() or a sumproduct() formula)

[#5]

@bperry1792 

@mtarler  built for you exactly what I was envisioning. He suggests that you "use that table to do a lookup to fill in the estimated end time:"

I'm going to take it a step further to suggest a specific way to access the numbers in that two-dimensional array, once they've been filled in. It's to use XLOOKUP, a newer function that allows you to use both the horizontal axis and the vertical axis to find the intersection. Here's a screen grab from exceljet.net that explains it.

You could also use OFFSET with two embedded MATCH functions to accomplish the same result, but I think the nested XLOOKUP is simpler.

You seem handy enough with Excel (given some of the other formulas you've written), but if you need more help, I'm sure either @mtarler or I could return to give that. I've got another pressing commitment at the moment, but wanted to give this additional resource. Here's the exceljet link for still more help with that function. https://exceljet.net/excel-functions/excel-xlookup-function

[#6]

@mtarler Thank you so much! The table idea proved to very helpful and that is exactly what I did. I had to create a new sheet for the 50+ different phases I had, but it worked wonderfully for the data that I needed to reference. 

[#7]

@mathetes wow, thank you so much! XLOOKUP proved to be just what I needed. By using the table I created thanks to @mtarler 's idea, I was able to use that function to calculate how many days were added to the start date based on a certain phase, and then was able to put a simple formula together to to add "x" amount of workdays to get my end date.

While we're here though...

Do either of you have experience with VBA's and Macros? I'm hoping to be able to set up automatic emails to notify users when a cell has been updated that is relevant to them. I did some research and found a ton of code that is based on numerical values, but I can't seem to find any that uses text values. For example, if the salesperson is "Keith Brown" and the "Current Department" phase turns "Sales_Design", I want him to be notified of that. And likewise, if it goes into a department that is not sales related (like Estimating), then I'd like the Estimating manager to be notified when the "Current Department" phase is updated to "Estimating".

Thanks again for taking time out of your day to work with this! 

[#8]

@bperry1792 I'm glad this worked out for you.  and thank you to @mathetes for the f/u post on a formula showing how to use that table.  There are so many options I was going to try a couple to see what I felt was the most versatile but easy to show but was also short on time, so I'm glad mathetes could hop in.  As for VBA I have experience but honestly never generated e-mails (not that it is hard).  I have no problem reviewing and tweaking a macro but not interested in creating it (I feel when I create VBA code/macro there is more ownership of it and therefore whenever something stops working or needs updating, well you get the idea and I already of 3 actual jobs and this is my 'fun' time).  That said, it sounds like you already found some sample code.  There isn't a ton difference between numbers and text when you are doing comparisons, you just can't do math with text, but with text you may use 'search' to see if a string is found inside another string (e.g. "Mathetes" might be in the spreadsheet as "@Mathetes", "Mathetes Inc", "Mathetes & Co.", etc and you want the find to catch any of them).

Lastly, if YOU try to do it you will learn a lot more than if someone does it for you.  So I recommend trying to do it, then make a new post (a new post will get better visibility for community members that do VBA to jump in) with what you are trying to do, the macro code you've done and questions about any help you need.  I hope I see that post and can help. 

[#9]

@bperry1792 

I have not actually written a macro for decades. Yes, I did do some back in the day, but not many even then.

So I second  @mtarler 's suggestion that you start a new thread asking specifically about macros that can generate an email under specified conditions.

And I'm glad that XLOOKUP worked so well for you.