Forum Discussion
Solved
Excel Query
Hi Team, In Column C are parts name and in Column D (yellow Highlighted) are sequenced of arriving them ( mentioned in cell E4,F4,G4). Since VB1 arrives on 1/1/2021 so it's sequence 1,1,1...
Use XLOOKUP() with BYROW() function like
=BYROW(E5:G23,LAMBDA(r,TEXT(XLOOKUP("z",r,$E$4:$G$4,,-1,-1),"d")))Only XLOOKUP() in this way-
=TEXT(XLOOKUP("F",E5:G5,$E$4:$G$4,,,-1),"d")Or-
=TEXT(XLOOKUP("z",E5:G5,$E$4:$G$4,,-1,-1),"d")See the attachment
Assuming:
- E4, F4, G4 contain the arrival dates for VB1, VB2, VB3 respectively.
- Column C contains values like “VB1”, “VB2”, or “VB3”.
Below the formula can place in Column D to calculate the sequence number for each part in Column C:
=MATCH(INDEX({$E$4,$F$4,$G$4},MATCH(C2,{"VB1","VB2","VB3"},0)),SORT({$E$4,$F$4,$G$4}),0)
- MATCH(C2,{"VB1","VB2","VB3"},0) finds the position of the part name.
- INDEX({$E$4,$F$4,$G$4},...) grabs the correct arrival date.
- SORT(...) sorts all dates from earliest to latest.
- MATCH(...,SORT(...),0) assigns 1 to earliest, 2 to next, 3 to latest.