Forum Discussion
VLOOKUP - COMPLEX FORMULA BASED ON START AND END DATES
Hi Jenny (Again!)
I am not sure I understand completely what you want but has attached a workbook with some cells shaded in green which have a vlookup formula. The answers i get don't match with yours so maybe it is not correct? have a look anyway.
Peter
Oh hi peteryac60 !
I'm 100% sure you can help me, but I'm finding it so difficult to explain what I need from the report (which is currently causing a lot of stress as no matter what I do, it does not provide the results I need due to the complexity of all the rules). Maybe I should start from scratch?
I need 4 columns:
2017
2018
2019
2020
In each column it must say either EC or Agent. The only way it can know which one, is by the start and end dates. If the start and end date ran across 2017, then I would want 2017 to show the contract type that year. Only exception to the rule, is, if a contract was 'Active' that year (you can only tell by going into the 'indirect tab), then I would want it say EC or Agent even if the start and end dates did not correspond. It is really very hard to explain 😞
Jenny
Let's break it down.
- The 4 columns are to be populated are in SalesForce Contract 22nd June. Is that right?
- We need to check the Indirect sheet to see if contract was active. For each Year column we check the corresponding year column on the Indirect sheet. For example, DOMAR is active for each year 2017-2020 according to Indirect sheet. Correct?
- How do you know if you are to populate EC or Agent? There is nothing in the Domar record either on SalesForce sheet or Indirect sheet which shows this?
- Do you need to check the Start/End date first or the Indirect sheet first. if the Indirect sheet , Domar is active for 4 years - where do we get the EC/Agent data.
if you can list the requirements it would be easier to understand the sequence.
thanks
peter
