Query on Excel

Occasional Contributor



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


Depends on which other values you have in cells, as variant it could be

earlist date (in P3):

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



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.