Index Match Nth Item

[JamesRo]

Hi, hopefully somebody can help in some way.
I have this formula: =IFERROR(INDEX(Customer_Weight_by_Date[Name_Weight],MATCH(1,(Customer_Weight_by_Date[tblOrderHeader.REQUIRED_DATE]=TODAY()-WEEKDAY(TODAY(),2)+1)*($A3=Customer_Weight_by_Date[Route]),0)),0)

which looks a this table:

tblOrderHeader.REQUIRED_DATE		Route	Customer Weight by Date	Short_Name	Name_Weight
17/04/2023		1	1158	SOUTHERN COU	SOUTHERN COU 1158
17/04/2023		5	1118	HERMITAGE GL	HERMITAGE GL 1118
17/04/2023		15	147	DRENCH EUROP	DRENCH EUROP 147
17/04/2023		15	296	MULTI-TURN L	MULTI-TURN L 296

And gives the result below...
You can see in route 15, DRENCH EUROP in E9 and F9, but I need a way to see the second (or nth) entry for that route on that date.
(hope that makes sense, I want to see MULTI-TURN L 296 in F9)
TIA

Copy of Copy of Daily Weights.xlsm
	A	B	C	D	E	F
1		Mon 17 Apr
2	Route	Area	Weight	Drops
3	1	Horsham	1158	1	SOUTHERN COU 1158
4	3	A3	314	8	0
5	30	A3	257	3	0
6	5	Basingstoke	1387	6	HERMITAGE GL 1118
7	6	Salisbury	208	4	0
8	88	Showerpower	174	1	0
9	15	Drench/Multi	443	2	DRENCH EUROP 147	DRENCH EUROP 147
10	17	Maxlen	109	1	0
Front

Cell Formulas
Range	Formula
B1	B1	=TEXT(TODAY()-WEEKDAY(TODAY(),2)+1,"ddd dd mmm")
C3:C9	C3	=SUMIFS(tblOrderHeader[TOTAL_WEIGHT],tblOrderHeader[Route],A3,tblOrderHeader[REQUIRED_DATE],TODAY()-WEEKDAY(TODAY(),2)+1)
D3:D9	D3	=IFERROR(INDEX(Merge1[Count],MATCH(1,(Merge1[REQUIRED_DATE]=TODAY()-WEEKDAY(TODAY(),2)+1)*($A3=Merge1[Route]),0)),0)
E3:E8,E10,E9:F9	E3	=IFERROR(INDEX(Customer_Weight_by_Date[Name_Weight],MATCH(1,(Customer_Weight_by_Date[tblOrderHeader.REQUIRED_DATE]=TODAY()-WEEKDAY(TODAY(),2)+1)*($A3=Customer_Weight_by_Date[Route]),0)),0)
C10	C10	=IFERROR(ROUND(JLOOKUP(TODAY()-WEEKDAY(TODAY(),2)+1,tblPurchaseOrderHeader[REQUIRED_DATE],tblPurchaseOrderHeader[Count]),0),0)
D10	D10	=IF(C10>0,1,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:C10,C13:C21,C24:C32,C35:C42,C45:C50,C55:C62,C65:C73,C76:C84,C87:C94,C97:C102,C107:C114,C117:C125,C128:C136,C139:C146,C149:C154,C159:C166,C169:C177,C180:C188,C191:C198,C201:C206	Cell Value	>1200	text	YES
C3:C10,C13:C21,C24:C32,C35:C42,C45:C50,C55:C62,C65:C73,C76:C84,C87:C94,C97:C102,C107:C114,C117:C125,C128:C136,C139:C146,C149:C154,C159:C166,C180:C188,C191:C198,C201:C206,C169:C177	Other Type	Color scale		NO

 

[Fluff]

For 2019 how about

=IFERROR(INDEX(Customer_Weight_by_Date[[Name_Weight]:[Name_Weight]],AGGREGATE(15,6,(ROW(Customer_Weight_by_Date[[Name_Weight]:[Name_Weight]])-ROW(Customer_Weight_by_Date[#Headers]))/(Customer_Weight_by_Date[[tblOrderHeader.REQUIRED_DATE]:[tblOrderHeader.REQUIRED_DATE]]=TODAY()-WEEKDAY(TODAY(),2)+1)/($A3=Customer_Weight_by_Date[[Route]:[Route]]),COLUMNS($E3:E3))),"")

 

[JamesRo]

Thanks once again. Is working, still trying to get my head around how it does...

 

[Fluff]

Glad to help & thanks for the feedback.