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,1,1.
Can somebody please help in finding a formula which helps in sequencing these parts from dates in cell E4,F4,G4 so VB2 has 2,2,2,2,2, and VB3 has 3,3,3,3,3.
- If VB1's date is earliest, it gets sequence 1
- If VB2's date is next, it gets sequence 2
- If VB3's date is latest, it gets sequence 3
Please help
thanks
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
2 Replies
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.
