Query on Excel

Copper Contributor

Sanket135_0-1609358466056.png

 

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??

 

3 Replies

@Sanket135 

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)

@Sergei Baklan 

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 datebatch numberBufferExpiry datebatch numberAmount purchasedExpiry datebatch NumberCostStock dispensed to daily dispensing unitexpiry dateBalance in handExpiry date (earliest)batch number of the earliest expiry
01-01-2012CAT1011-02-2012BAT1029-09-2011MAT 5 2529-09-2011????
           0  

 

@Sanket135 

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.