How to Create a Formula Based on Multiple Conditions

Question

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:&nbsp;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.&nbsp;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?&nbsp;Thanks in advance!&nbsp;

mathetes · Answer

@bperry1792&nbsp;&nbsp;mtarler&nbsp; 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:"&nbsp;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.&nbsp;You could also use OFFSET with two embedded MATCH functions to accomplish the same result, but I think the nested XLOOKUP is simpler.&nbsp;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&nbsp;&nbsp;

mtarler · Answer

bperry1792&nbsp;I'm glad this worked out for you.&nbsp; and thank you to&nbsp;mathetes&nbsp;for the f/u post on a formula showing how to use that table.&nbsp; 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.&nbsp; As for VBA I have experience but honestly never generated e-mails (not that it is hard).&nbsp; 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).&nbsp; That said, it sounds like you already found some sample code.&nbsp; 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 &amp; 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.&nbsp; 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.&nbsp; I hope I see that post and can help.&nbsp;

mathetes · Answer

bperry1792&nbsp;&nbsp;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)......&nbsp;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.

bperry1792 · Answer

mathetes&nbsp;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.&nbsp;

mtarler · Answer

bperry1792&nbsp; so I tweaked your reference sheet to have Phase and Level making a table.&nbsp; 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)

Forum Discussion

How to Create a Formula Based on Multiple Conditions

8 Replies

Resources