Returning name on multiple date arrays

[archibell]

I know how to do an index match when the date is static (i.e. put a date and it returns the name), but not sure how to write the formula with multiple dates because of the lack of ability to change the array within the formula. I am sure I am missing something simple. Here is the example of the data. The promo's run between certain dates and I am trying to match weeks of syndicated data that use the 'WE DATE' to what promo might have run during that week.

NAME	START	END		WS DATE	WE DATE	PROMO (OUTPUT)
PROMO 1	01/24/23	02/15/23		01/01/23​	01/07/23​	-
PROMO 2	02/16/23	03/08/23		01/08/23​	01/14/23​	-
PROMO 3	03/09/23	03/29/23		01/15/23​	01/21/23​	-
PROMO 4	03/23/23	04/12/23		01/22/23​	01/28/23​	PROMO 1
PROMO 5	04/04/23	04/24/23		01/29/23​	02/04/23​	PROMO 1
PROMO 6	04/25/23	05/15/23		02/05/23​	02/11/23​	PROMO 1
PROMO 7	05/16/23	06/07/23		02/12/23​	02/18/23​	PROMO 2
PROMO 8	06/06/23	06/28/23		02/19/23​	02/25/23​	PROMO 2

 

[dreid1011]

So, you are looking to see which promo period the WE DATE falls in? What about WS DATE? And what about when a promo period overlaps?

 

[archibell]

Correct. The WS date is just a column I added but the syndicated data only shows the WE date. Overlaps should be minimal but they do exist, so great question. I did think about having an additional column for secondary promos...open to suggestions on how to handle those!

 

[dreid1011]

Correct. The WS date is just a column I added but the syndicated data only shows the WE date. Overlaps should be minimal but they do exist, so great question. I did think about having an additional column for secondary promos...open to suggestions on how to handle those!

Not entirely sure, but here are a couple options depending on what version of excel you are using and what you want to return for the first three results.

Book1 w Stock Room 12-7-2023.xlsm
	A	B	C	D	E	F	G	H	I
1	NAME	START	END		WS DATE	WE DATE	PROMO (OUTPUT)	Formula Result EXCEL 365	Formula Result Pre 365
2	PROMO 1	1/24/2023	2/15/2023		01/01/23	1/7/2023	-	PROMO 1	-
3	PROMO 2	2/16/2023	3/8/2023		01/08/23	1/14/2023	-	PROMO 1	-
4	PROMO 3	3/9/2023	3/29/2023		01/15/23	1/21/2023	-	PROMO 1	-
5	PROMO 4	3/23/2023	4/12/2023		01/22/23	1/28/2023	PROMO 1	PROMO 1	PROMO 1
6	PROMO 5	4/4/2023	4/24/2023		01/29/23	2/4/2023	PROMO 1	PROMO 1	PROMO 1
7	PROMO 6	4/25/2023	5/15/2023		02/05/23	2/11/2023	PROMO 1	PROMO 1	PROMO 1
8	PROMO 7	5/16/2023	6/7/2023		02/12/23	2/18/2023	PROMO 2	PROMO 2	PROMO 2
9	PROMO 8	6/6/2023	6/28/2023		02/19/23	2/25/2023	PROMO 2	PROMO 2	PROMO 2
Sheet6

Cell Formulas
Range	Formula
H2:H9	H2	=XLOOKUP(F2,$C$2:$C$9,$A$2:$A$9,"",1)
I2:I9	I2	=IFNA(LOOKUP(F2,$B$2:$B$9,$A$2:$A$9),"-")

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[archibell]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

365. Updated the details.

 

[Fluff]

Thanks for that, have to tried the suggestions by dreid1011?

 

[archibell]

They do work and marked as a solution. The result is not 100% like I would like but it does work so I am going with it until I think of a better solution.

 

[dreid1011]

They do work and marked as a solution. The result is not 100% like I would like but it does work so I am going with it until I think of a better solution.

We can make adjustments to get as close to what you want if you let us know what is not working.

 

[archibell]

We can make adjustments to get as close to what you want if you let us know what is not working.

Sure thing. My main issue is that I just haven't had the time to return to this problem and see what the output looks like on the full data set. Where I can foresee issues arising is that using the xlookup function, the weeks that we have promotions not running will still show a promotion name on them (i.e. the weeks prior to Promo 1 on the example table) It looks like the IFNA function solves that issue.

When I was originally looking into it I was using a formula similar to this:

=IFERROR(INDEX($A$2:$A$9,MATCH(1,(F2>=$B$2:$B$9)*(F2<=$C$2:$C$9),0),1),"-")

I must have typed something wrong because on the original sheet it was giving me an error, but in my example it seems to work well.