VLOOKUP - COMPLEX FORMULA BASED ON START AND END DATES

[JennySommet]

Hi everyone,

This is very complex! In columns U-X of attached excel, I want the result to show the contract type of the account (column H), but it should also match the contract type it was THAT YEAR. I don't know if this is possible. Each Account in column A has several contracts - hence the multiple lines. I want columns U-X to always show the contract type in a given year, regardless of what the start and end date on the specific line is. I hope this makes sense! This report is driving me insane :(

I have put the correct result it should show below.

Thank you so much!

[#2]

Hi, 

Have you try to make another column with CONCATENATE ? You could have in this new column the contract type & the year of the contract, so that you have a new unique information, that you could match with the same column created in your database? 

Baptiste 

[#3]

Thanks for your reply- I am not sure how that would work. I need each column to reflect the contract type it was THAT year, no matter what the specific start and end dates on the row says.

[#4]

Hey friend, Can you explain the issue in more details as above nothing is clearly understood. Also please try connecting with me then I would explain or solve your issue@JennySommet 

[#5]

@JennySommet 

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

[#6]

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 :(

[#7]

@JennySommet 

Jenny

Let's break it down.

1. The 4 columns are to be populated are in SalesForce Contract 22nd June. Is that right?
2. 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?
3. 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?
4. 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