INDEX MATCH IF Between Two Dates - Comparing Two Tables

[#N/A]

Suppose you have two tables laid out like this. How can you query each row on Table 2 against Table 1 and return a MATCH result?

I've tried =MATCH(1,([@Employee]=Table1[Employee])*([@[Leave Type]]=Table1[Leave Type])*OR(([@[Leave Date]]=Table1[Leave Start]),[@[Leave Date]]=Table1[Leave Finish],AND([@[Leave Date]]>Table1[Leave Start],[@[Leave Date]]<Table1[Leave Finish])),0) but the AND section doesn't return TRUE for Table 2 row 5 even though 1/3/2015 is greater than 1/2/2015 and less than 1/4/2015.

See attachment for prepared data.

What am I missing here? Is there a better way?

Table 1
Employee Leave Type Leave Start Leave Finish
A Sick 1/1/2015
A Annual 1/2/2015
B Sick 1/1/2015
B Sick 1/2/2015 1/4/2015
B Sick 1/5/2015 1/9/2015
B Sick 1/11/2015
C Annual 1/5/2015 1/9/2015
C Flex 1/11/2015 1/15/2015

Table 2
Employee Leave Type Leave Date
A Sick 1/1/2015
A Annual 1/2/2015
B Sick 1/1/2015
B Sick 1/2/2015
B Sick 1/3/2015
B Sick 1/4/2015
B Sick 1/5/2015
B Sick 1/6/2015
B Sick 1/7/2015
B Sick 1/8/2015
B Sick 1/9/2015
B Sick 1/11/2015
C Annual 1/5/2015
C Annual 1/6/2015
C Annual 1/7/2015
C Annual 1/8/2015
C Annual 1/9/2015
C Flex 1/11/2015
C Flex 1/12/2015
C Flex 1/13/2015
C Flex 1/14/2015
C Flex 1/15/2015

[BuZZarD73]

Attachment appears to be missing - if you could upload it I'll take a look.

Are you wanting to know if an entry from table 1 is within a date range of an entry in table 2?
What's the exact criteria/logic you want to apply?

[samba_ravi]

=SUMPRODUCT(MAX((Table1[Employee]=Table2[[#This Row],[Employee]])*(Table1[Leave Type]=Table2[[#This Row],[Leave Type]])*(Table1[Leave Start]<=Table2[[#This Row],[Leave Date]])*((Table1[Leave Finish]+(Table1[Leave Finish]=0)*Table1[Leave Start])>=Table2[[#This Row],[Leave Date]])*(ROW(Table1[Leave Start])-MIN(ROW(Table1[Leave Type]))+1)))

Please Login or Register  to view this content.

TRY THE ABOVE FORMULA

[#N/A]

Hi,

Sorry the attachment is available now.

Essentially, I want to see if all entries in Table 2 exist in Table 1.

So my logic is: IF
Table 2 Employee = Table 1 Employee AND
Table 2 Leave Type = Table 2 Leave Type AND
(
Table 2 Leave Date = Table 1 Leave Start OR
Table 2 Leave Date = Table 1 Leave Finish OR
(
Table 2 Leave Date > Table 1 Leave Start AND
Table 2 Leave Date < Table 2 Leave Finish
)
)
IS TRUE or FALSE.

Thanks for helping.

[#N/A]

=SUMPRODUCT(MAX((Table1[Employee]=Table2[[#This Row],[Employee]])*(Table1[Leave Type]=Table2[[#This Row],[Leave Type]])*(Table1[Leave Start]<=Table2[[#This Row],[Leave Date]])*((Table1[Leave Finish]+(Table1[Leave Finish]=0)*Table1[Leave Start])>=Table2[[#This Row],[Leave Date]])*(ROW(Table1[Leave Start])-MIN(ROW(Table1[Leave Type]))+1)))

Please Login or Register  to view this content.

TRY THE ABOVE FORMULA

Wow. I don't know how you got that so quick! But huge thanks!

I had the impression SUMPRODUCT doesn't work with text.

[samba_ravi]

You are welcome and thanks for your feedback