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]

How about

=TRANSPOSE(FILTER(Customer_Weight_by_Date[Name_Weight],(Customer_Weight_by_Date[tblOrderHeader.REQUIRED_DATE]=TODAY()-WEEKDAY(TODAY(),2)+1)*($A3=Customer_Weight_by_Date[Route]),0))

 

[JamesRo]

oh that's clever, thank you

 

[Fluff]

You're welcome & thanks for the feedback.

 

[JamesRo]

Sorry!
For my next question.
Is there a way to limit how many columns I will populate to the right. e.g. a maximum of 5 entries?
Thanks.

 

[Fluff]

How about

=LET(f,FILTER(Customer_Weight_by_Date[Name_Weight],(Customer_Weight_by_Date[tblOrderHeader.REQUIRED_DATE]=TODAY()-WEEKDAY(TODAY(),2)+1)*($A3=Customer_Weight_by_Date[Route]),0),INDEX(f,SEQUENCE(,MIN(ROWS(f),5))))

 

[JamesRo]

Thanks again. Super quick answer too!

 

[Fluff]

You're welcome.

 

[JamesRo]

This works great, however we have encountered a problem in that the sheet is shared with some users that are still using Excel 2019.
Is there any way around this at all without upgrading?
Thanks.

 

[JamesRo]

Thinking if I could somehow get only the highest 4 "Customer weight by date", for each date and route but I can't see a way to do this in the query editor.
I could then use the TRANSPOSE formula above.