Lookup with multiple criteria between two sheets

Question

I have two sheets

Sales <tblSales>

Inventory<tblInventory>

In sheet inventory

I have14 columns data. But I am interested in column Code. It is auto generated.

Like WT-B-1 for same product but if same new stock purchase WT-B-2. It is already working.

In sheet Sales

I have multiple columns.

I have to choose product then name <It is tabular data>

Sales B2 is, Product: Water Tank <WT>

Sale B3 is Name, Bash <B>

Sale C3 is my problem.

Problem:

I want to auto pick Code assigned to that stock in Sheet Inventory.

Lets suppose,

If WT-B-1 purchased 100 Units.

Then Code WT-B-1 will auto extracted to Sale C3 until stock becomes 0 for WT-B-1.

Then for next Sale, Sale C3 will shift to WT-B-2 <It represents same product, new stock>.

Data validation, if old stock is only 1 unit but user enter 2 units, it will not be allowed. Instead it suggests that only 1 unit left. Split in 2 sales entry. Choose 1 for old stock and 2nd for new stock.

Hope you understand Sir/Madam.

Thanks