Merging 2 tables to track Employee sales during different periods and locations

[naira]

Hi!

I need to merge 2 tables, one of which has Employee IDs and their postings at various locations during the time periods with start and end dates for posting.
Table 2 has the sales made by the respective employees on a particular date and the amount of sale.

I need to merge both the tables so as to get the Employee no., date of sale, sale value and the location at which sale was made.

I tried using combinations of vlookup, hlookup, index, match etc. but am stuck since there are multiple date entries/ more than one matching criteria between the 2 tables.

Note: Sample file attached

[sandy666]

Update your profile about Excel version, because Ex2003 doesn't support xlsx extension

[JohnTopley]

AS "Result table" is "Table2" with location added

try ...

location ..

in C20


=INDEX($D$3:$D$15,SMALL(IF(($A$3:$A$15=A20)*($B$3:$B$15<=B20)*($C$3:$C$15>=B20),ROW($A$3:$A$15)-ROW($A$3)+1,""),1))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[naira]

Works beautifully.
Thanks a lot!!!