Dec 30 2020 12:05 PM
Would anyone be kind enough to help me with the function to get the related batch number of the lowest expiry date.. which I have marked as Red Question marks??
Dec 30 2020 01:24 PM
Depends on which other values you have in cells, as variant it could be
earlist date (in P3):
=MINIFS(D3:L3,D3:L3,">1000")
batch number:
=INDEX(D3:L3, XMATCH(P3,D3:L3)+1)
Jan 01 2021 08:08 AM
Thank you for your reply, but I wanted to know that, I have got the "Earliest expiry date" by the function =MIN(J3,G3,D3) ...now I want the related batch number which is adjacent to that date in the last cell which is "Batch Number of the earliest expiry" for example
Here the Expiry Date(Earliest) is 29-09-2011 which I have got by the function =MIN(J3,G3,D3), now I want the batch Batch Number of the earliest expiry which is "MAT", in the below example in the last column, I have marked "?" question mark for your convenience in the below example.
I would be glad if you kindly provide me a solution.
Expiry date | batch number | Buffer | Expiry date | batch number | Amount purchased | Expiry date | batch Number | Cost | Stock dispensed to daily dispensing unit | expiry date | Balance in hand | Expiry date (earliest) | batch number of the earliest expiry |
01-01-2012 | CAT | 10 | 11-02-2012 | BAT | 10 | 29-09-2011 | MAT | 5 | 25 | 29-09-2011 | ???? | ||
0 |
Jan 02 2021 07:45 AM
Formula as in my previous post. If you are on Excel version without XMATCH(), you may use
=INDEX(D4:L4, MATCH(P4,D4:L4,0)+1)
Both formulas are within attached file.