Index Match Nth Item

JamesRo

New Member
Joined
Jun 30, 2022
Messages
26
Office Version
  1. 2021
Platform
  1. Windows
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_DATERouteCustomer Weight by DateShort_NameName_Weight
17/04/202311158SOUTHERN COUSOUTHERN COU 1158
17/04/202351118HERMITAGE GLHERMITAGE GL 1118
17/04/202315147DRENCH EUROPDRENCH EUROP 147
17/04/202315296MULTI-TURN LMULTI-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
ABCDEF
1Mon 17 Apr
2RouteAreaWeightDrops
31Horsham11581SOUTHERN COU 1158
43A331480
530A325730
65Basingstoke13876HERMITAGE GL 1118
76Salisbury20840
888Showerpower17410
915Drench/Multi4432DRENCH EUROP 147DRENCH EUROP 147
1017Maxlen10910
Front
Cell Formulas
RangeFormula
B1B1=TEXT(TODAY()-WEEKDAY(TODAY(),2)+1,"ddd dd mmm")
C3:C9C3=SUMIFS(tblOrderHeader[TOTAL_WEIGHT],tblOrderHeader[Route],A3,tblOrderHeader[REQUIRED_DATE],TODAY()-WEEKDAY(TODAY(),2)+1)
D3:D9D3=IFERROR(INDEX(Merge1[Count],MATCH(1,(Merge1[REQUIRED_DATE]=TODAY()-WEEKDAY(TODAY(),2)+1)*($A3=Merge1[Route]),0)),0)
E3:E8,E10,E9:F9E3=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)
C10C10=IFERROR(ROUND(JLOOKUP(TODAY()-WEEKDAY(TODAY(),2)+1,tblPurchaseOrderHeader[REQUIRED_DATE],tblPurchaseOrderHeader[Count]),0),0)
D10D10=IF(C10>0,1,0)
Cells with Conditional Formatting
CellConditionCell FormatStop 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:C206Cell Value>1200textYES
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:C177Other TypeColor scaleNO
 
For 2019 how about
Excel Formula:
=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))),"")
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Thanks once again. Is working, still trying to get my head around how it does... :)
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top