Selecting which data to input

Hi,

I was hoping someone may be able to help.

I've created a Gantt chart to plot different data from a table into the corresponding dates and rows. However it's not picking out the correct reference number.

I need to plot when our construction teams (gangs) are attending which project on which dates. I've managed to get the data to plot on the Gantt chart in the correct dates, however I now need it to plot the correct gang reference.

I'll attach the file in the comment below.

So, for example, in the table I may have this data:

PROJECT NO. GANG NO. START END REF
Project 1, Gang 1, 01/01/2020, 05/01/2020, G1
Project 1, Gang 2, 06/01/2020, 10/01/2020, G2
Project 2, Gang 1, 06/01/2020, 10/01/2020, G1

At the moment its plotting on the chart the correct dates, regardless of the location of the data in the table. So it has G1 on the Gantt chart in line with project 1 between the dates 01/01/2020 and 05/01/2020. However it's also plotting G1 in line with project 1 in the dates 06/01/2020 and 10/01/2020, however I need it to display G2.

There must be a part of the formula I need to amend so that as well as picking up the project and dates it will also pick out the correct reference number.

As I say hopefully the example in my comment and attached file below will help explain.

As you can see in the attachment in the "Gantt Chart" tab I've used the formula:

=IF(SUMPRODUCT(('Gang Allocations'!$A1:$A180=$A21)*('Gang Allocations'!$F1:$F180<=G$20)*('Gang Allocations'!$G1:$G180>=G$20))>0,VLOOKUP($A21,Table1,8,FALSE),""

This is pulling the data required from the "gang allocations" tab.

As I say I now need it to cross reference so that whatever the gang reference is that corresponds to the dates, it plots that reference. At the moment I believe it's simply searching the table and plotting the first gang reference it comes across with the project number.

I've been using only the top couple of rows on the Gantt chart so many of the other cells have other formulas in that I've been experimenting with which you may find of use.