SOLVED

Auto Data Match

%3CLINGO-SUB%20id%3D%22lingo-sub-2026674%22%20slang%3D%22en-US%22%3EAuto%20Data%20Match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2026674%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%20Happy%20New%20Year%20to%20everyone.%3C%2FP%3E%3CP%3EI%20have%20a%20query%20and%20asking%20for%20help...%3C%2FP%3E%3CP%3EMy%20query%20was%2C%20I%20have%20got%20the%20%22%3CSTRONG%3EEarliest%20expiry%20date%22%3C%2FSTRONG%3E(Cell%20No%20P3)%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eby%20the%20function%3CSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3DMIN(J3%2CG3%2CD3)%3C%2FSTRONG%3E%26nbsp%3B...now%20I%20want%20the%20related%20batch%20number%20which%20is%20adjacent%20to%20that%20date%20in%20the%20last%20column%20which%20is%20%22%3CSTRONG%3EBatch%20Number%20of%20the%20earliest%20expiry%22%3C%2FSTRONG%3E(Cell%20No%20Q3)%2C%26nbsp%3Bfor%20example%20...%3C%2FP%3E%3CP%3E%26nbsp%3BHere%20the%20Expiry%20Date(Earliest)is%2029-09-2011%20which%20I%20have%20got%20by%20the%20function%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E%3DMIN(J3%2CG3%2CD3)%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSTRONG%3Ein%26nbsp%3B%3CSTRONG%3EExpiry%20Date(Earliest)%20column%26nbsp%3B%3C%2FSTRONG%3E(Cell%20No%20P3)%3CSTRONG%3E%2C%26nbsp%3B%3C%2FSTRONG%3Enow%20I%20want%20the%20Batch%20Number%20of%2029-09-2009%20which%20is%20%22%3CSTRONG%3EAAC%22%3C%2FSTRONG%3E(Cell%20No%20H3)(Adjacent%20to%2029-09-2011)%2C%20to%20be%20shown%20in%20the%20last%20column%20which%20is%20the%20%22%3CSTRONG%3EBatch%20Number%20of%20the%20earliest%20expiry%22%3C%2FSTRONG%3E(Cell%20No%20Q3)automatically%2C%20I%20have%20marked%20%22%3F%22%20(question%20mark)%20in%20the%20below%20example.)%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CTABLE%20width%3D%221064%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2289%22%3EStock%20in%20hand%3C%2FTD%3E%3CTD%20width%3D%2290%22%3EExpiry%20date%3C%2FTD%3E%3CTD%20width%3D%2253%22%3Ebatch%20number%3C%2FTD%3E%3CTD%20width%3D%2266%22%3EBuffer%3C%2FTD%3E%3CTD%20width%3D%2279%22%3EExpiry%20date%3C%2FTD%3E%3CTD%20width%3D%2255%22%3Ebatch%20number%3C%2FTD%3E%3CTD%20width%3D%2269%22%3EAmount%20purchased%3C%2FTD%3E%3CTD%20width%3D%2282%22%3EExpiry%20date%3C%2FTD%3E%3CTD%20width%3D%2258%22%3EBatch%20Number%3C%2FTD%3E%3CTD%20width%3D%2278%22%3ECost%3C%2FTD%3E%3CTD%20width%3D%2229%22%3EStock%20dispensed%20to%20daily%20dispensing%20unit%3C%2FTD%3E%3CTD%20width%3D%2277%22%3Eexpiry%20date%3C%2FTD%3E%3CTD%20width%3D%2240%22%3EBalance%20in%20hand%3C%2FTD%3E%3CTD%20width%3D%22105%22%3EExpiry%20date%20(earliest)%3C%2FTD%3E%3CTD%20width%3D%2294%22%3Ebatch%20number%20of%20the%20earliest%20expiry%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E01-01-2012%3C%2FTD%3E%3CTD%3EBBT%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E12-01-2009%3C%2FTD%3E%3CTD%3EAAC%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E29-09-2011%3C%2FTD%3E%3CTD%3EFFF%3C%2FTD%3E%3CTD%3E250%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E01-01-2021%3C%2FTD%3E%3CTD%3E17%3C%2FTD%3E%3CTD%3E12-01-2009%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23FF0000%22%3E%26nbsp%3B%3F%3F%3F%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EI%20would%20be%20glad%20if%20you%20kindly%20provide%20me%20a%20solution.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2026674%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2026726%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Data%20Match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2026726%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F914543%22%20target%3D%22_blank%22%3E%40Sanket135%3C%2FA%3E%26nbsp%3BGuessing%20the%20exact%20cell%20references%2C%20but%20I%20believe%20you%20need%20to%20enter%20the%20following%20formula%20in%20Q3%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(C3%3AK3%2C1%2CMATCH(P3%2CC3%3AK3%2C0)%2B1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2026740%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Data%20Match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2026740%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F914543%22%20target%3D%22_blank%22%3E%40Sanket135%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDEX(E3%3AK3%2CMATCH(P3%2CD3%3AJ3%2C0))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2026751%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Data%20Match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2026751%22%20slang%3D%22en-US%22%3EThank%20You%20so%20much%2C%20does%20the%20magic.%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello Everyone, Happy New Year to everyone.

I have a query and asking for help...

My query was, I have got the "Earliest expiry date"(Cell No P3) by the function =MIN(J3,G3,D3) ...now I want the related batch number which is adjacent to that date in the last column which is "Batch Number of the earliest expiry"(Cell No Q3), for example ...

 Here the Expiry Date(Earliest)is 29-09-2011 which I have got by the function =MIN(J3,G3,D3) in Expiry Date(Earliest) column (Cell No P3)now I want the Batch Number of 29-09-2009 which is "AAC"(Cell No H3)(Adjacent to 29-09-2011), to be shown in the last column which is the "Batch Number of the earliest expiry"(Cell No Q3)automatically, I have marked "?" (question mark) in the below example.)

Stock in handExpiry datebatch numberBufferExpiry datebatch numberAmount purchasedExpiry dateBatch NumberCostStock dispensed to daily dispensing unitexpiry dateBalance in handExpiry date (earliest)batch number of the earliest expiry
1001-01-2012BBT212-01-2009AAC1029-09-2011FFF250501-01-20211712-01-2009 ???

I would be glad if you kindly provide me a solution.

4 Replies
best response confirmed by Sanket135 (Occasional Contributor)
Solution

@Sanket135 Guessing the exact cell references, but I believe you need to enter the following formula in Q3

 

=INDEX(C3:K3,1,MATCH(P3,C3:K3,0)+1)

 

Added a workbook for your convenience

@Sanket135 

 

=INDEX(E3:K3,MATCH(P3,D3:J3,0))

Thank You so much, does the magic.
Thanks a lot, Have a nice day.