VLOOKUP + IF + OR with 4 conditions, using 2 datasets.

Hi All!

I am struggling with a formula where I think I need an Vlookup & IF & OR statements for, but I am not sure.
So this is the situation:

Dataset 1:
Column A: Title
Column B: Date
Column C: Where the Formula should show a Number or UNKNOWN

Dataset 2:
Column A: Title
Column B: Start Date
Column C: End Date
Column D: Number

I want it to work like this: If the title in Dataset 1 matches the title in Dataset 2 AND (if the Date from Dataset 1 falls between the start and end date from Dataset 2 OR the date from Dataset 1 falls after the startdate from dataset 2 and the enddate from dataset 2 is empty), I want to show the number (Column D) from dataset 2. If else: show the word 'UNKNOWN'.

=IF(ISNA(MATCH($A2,'[Dataset 2.xlsx]Sheet1'!$A:$A,0)),"not present",

IF(AND($B2>=VLOOKUP($A2,'[Dataset 2.xlsx]Sheet1'!$A:$D,2,FALSE),

OR(ISEMPTY($B2); $B2<=VLOOKUP($A2,'[Dataset 2.xlsx]Sheet1'!$A:$D,3,FALSE))),

VLOOKUP($A2,'[Dataset 2.xlsx]Sheet1'!$A:$D,4,FALSE),"UNKNOWN"))

I tried changing the formula to this, but for some reason its not working properly.



I Added two dummy datasets to show what should happen > Dataset 2_NEW.xlsxDataset 1_NEW.xlsx

Please help !

Thanks.

Try these

Formula:

Please Login or Register  to view this content.

See the file Dataset 1_NEW(1).xlsx

With that formula I lose information whether the data is Not Present or Unknown. The third condition should split up in ">= Enddate OR empty cell". I tried the formula shown below. It doenst give an error, but its not working either..

Formula:

Please Login or Register  to view this content.

Can anyone help?

I just tried this formula anyhow, but I noticed that it SUMS the numbers of the row with the same names in it. So for example, If I have 'Carl' twice and the first number says 7 and the other one says 11, then it gives me 18.. This shouldnt happen. It should look at the dates and create the number that is in that row.

Does anyone have a solution?

I dont understand.
With the data that you present in "Dataset 1_New" my formula produces the results that were expected by you.
Please include the situation that causes error.

Hi Jose,

Thanks for getting back at me (!)

The errors are shown in the files attached Dataset 2_NEW.xlsxDataset 1_NEW.xlsx. I made 2 columns: 1 showing what I want it to show, and another one with your calculation in it. I hope this clarifies what Im looking for.

Hope you (or someone else) can help.

Hi EndeHerc,

see the attached excel file if it will work for you.

Thanks
Rohit