INDEX/MATCH on 1 column + date in range

[Twatwood]

Hi

I tried to use the INDEX and MATCH stuff, but cannot work out how to do this where Variable1 in STAFF and Variable2 between DATEFROM and DATETO

Data

STAFF	DATEFROM	DATETO	TEAM
AB	01/11/2016	30/06/2021	TEAM1
ANV	01/11/2016	08/12/2016	TEAM1
ANV	09/11/2016	30/06/2021<strike></strike>	TEAM2
DS	01/11/2016<strike></strike>	30/06/2021<strike></strike>	TEAM2
JMG	01/11/2016<strike></strike>	30/06/2021<strike></strike>	TEAM1

<tbody>
</tbody>

I want to retrieve the TEAM for give Staff (e.g. ANV) where the DATE (e.g. 22/11/2016) is between DATEFROM and DATETO

Any suggestions?

Thanks
TW

 

[Aladin Akyurek]

What are the expected results?

 

[Canapone]

Hi,

in D1:D6 teams, in F1 "ANV", in G1 22/11/2016

In G2 to be copied down (two Teams fall in the conditions)

[TABLE="width: 1251"]
<tbody>[TR]
[TD="class: xl65, width: 1251"]
=IFERROR(INDEX($D$1:$D$6,AGGREGATE(15,6,ROW($A$1:$A$6)/(($A$1:$A$6=$F$1)*($B$1:$B$6<=$G$1)*($C$1:$C$6>=$G$1)),ROWS($A$1:$A1))),"")

[/TD]
[/TR]
</tbody>[/TABLE]

Regards

 

[Twatwood]

Expected result;

If staff ID is 'ANV' and date is 8 Dec, then will retrieve TEAM1
If staff ID is 'ANV' and date is 9 Dec, then will retrieve TEAM2

Oops, noticed a typo in the Data table; there's not supposed to be overlapping date ranges.

 

[Aladin Akyurek]

Expected result;

If staff ID is 'ANV' and date is 8 Dec, then will retrieve TEAM1
If staff ID is 'ANV' and date is 9 Dec, then will retrieve TEAM2

Oops, noticed a typo in the Data table; there's not supposed to be overlapping date ranges.

Let F2 house an id of interest like ANV and G2 a date of interest like 2016-11-22.

In H2 control+shift+enter, not just enter:

=INDEX($D$2:$D$6,MATCH(F2,IF(G2>=$B$2:$B$6,IF(G2<=$C$2:$C$6,$A$2:$A$6)),0))

If multiple teams are a possibility and you are on a 2016 Excel version...

In H2 control+shift+enter:

=TEXTJOIN(", ",TRUE,IF($A$2:$A$6=F2,IF(G2>=$B$2:$B$6,IF(G2<=$C$2:$C$6,$D$2:$D$6,""),""),""))

 

[Twatwood]

In my spreadsheet I named the columns for reference as the ranges are quite large (2000+ rows) and on a different worksheet.

StaffCode	DateFrom	DateTo	TeamName
ANV	01/11/2016	08/12/2016	TEAM1
ANV	09/12/2016	30/06/2020	TEAM2

<tbody>
</tbody>

<strike></strike>

Let $D35 = 'ANV' and $F35 = '05/12/2016'
Let $D36 = 'ANV' and $F36 = '15/12/2016'

In cells G35 I put the formula
=INDEX(TeamCode,MATCH($D35,IF($F35>=DateFrom,IF($F35<=DateTo,StaffCode)),0))
the value returned in "TEAM1" -- as expected

In cell G36 I put the formula
=INDEX(TeamCode,MATCH($D36,IF($F36>=DateFrom,IF($F36<=DateTo,StaffCode)),0))
the value returned in "TEAM1" -- not as expected

I cannot see where I went wrong.

 

[Aladin Akyurek]

You need to confirm the formula with control+shift+enter, not just enter. That is, press down the shift and the control keys at the same time, while you hit the enter kay.