# Query on Excel

Occasional Contributor

# Query on Excel

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

# Re: Query on Excel

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)
``````

# Re: Query on Excel

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

# Re: Query on Excel

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.