Selected Answer
Hello again,
Like what I understood I tried one approach (in the attached file)
I made one column that give you the sum of IN quantity before this IN and another column that gives you the sum after this new quantity,
So when you type the OUT it will search this quantity in the 2 columns to find a row that this new quantity is more than 1st column and less than 2nd column,
When it finds that row it will give The N number of this row (new column too),
Now, it will use Index match formula to index the Lot. No.
Sorry if this is complicated,
PLEASE try the attached file and mark as answer if that helped you.
(I'm not sure if using nonaccurate match with 1st sum column may shorten that but I don't use it nor recommend it)
Note: I'm not sure if date,balance quantity,batch no are important, I considered that they are not.