VLOOKUP + AND/IF with 3 conditions, using 2 datasets.

Hi All!

I am struggling with a formula where I think I need an Vlookup & AND/IF 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, I want to show the number (Column D) from dataset 2. If else: show the word 'UNKNOWN'.

I Added two dummy datasets to show what should happen > Dataset 1.xlsx Dataset 2.xlsx (I Accidently added column E & F in dataset 2, please ignore these columns)

Please help !

Thanks.

Try

=IFERROR(VLOOKUP(A2,IF(AND(B2>='[Dataset 2.xlsx]Sheet1'!B2:B6,B2<='[Dataset 2.xlsx]Sheet1'!C2:C6),'[Dataset 2.xlsx]Sheet1'!A2:D6),4,0),"UNKNOWN")

It seems to be working but do a lot of testing with other data, you might need to make this an array formula (use Ctrl-Shift-Enter).

A variation on a theme:

Formula:

Please Login or Register  to view this content.

Regards, TMS

@Special-K: I don't think your formula will work if the names are in a different order in the two datasets.

My formula distinguishes between "not present" and "unknown"

Not sure what will happen in either formula if names are duplicated.

Regards, TMS

UPDATE: Corrected my formula, it SHOULD be Array Formula (Ctrl-Shift-Enter)

=IFERROR(VLOOKUP(A2,IF(B2>='[Dataset 2.xlsx]Sheet1'!B$2:B$6,IF(B2<='[Dataset 2.xlsx]Sheet1'!C$2:C$6,'[Dataset 2.xlsx]Sheet1'!A$2:D$6)),4,0),"UNKNOWN")

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

That's not the way it works. The question has been asked and answered, hence ... solved.

If you have a new question, albeit as a follow up to the original question, please start a new thread.

Regards, TMS