Auto Data Match

[Sanket135]

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 hand

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

10

01-01-2012

BBT

2

12-01-2009

AAC

10

29-09-2011

FFF

250

5

01-01-2021

17

12-01-2009

???

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

[#2 · Accepted 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

[#3]

@Sanket135 

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

[#4]

Thank You so much, does the magic.

[#5]

Thanks a lot, Have a nice day.