Lookup with multiple criteria between 2 sheets

[MansoorAli]

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.

Sales Table Example

Date	Product	Name	Quantity	Code
Any	Table	Wooden	100	T-W-1
Any	Table	Wooden	5	T-W-2(Expected out)
Any	Chair	Fiber	50	T-W-1
Any	Chair	Fiber	5	T-W-2(Expected output)

Inventory Table Example

Date	Product	Name	Code	Quantity
Any	Table	Wooden	T-W-1	100
Any	Chair	Fiber	C-F-1	20
Any	Chair	Fiber	C-F-2	50
Any	Table	Wooden	T-W-2	30

Thanks

[#2]

@MansoorAli 

To achieve this, you can maybe use a combination of Excel functions like INDEX, MATCH, and IFAssuming your Sales table starts from cell A1 in the Sales sheet, and your Inventory table starts from cell A1 in the Inventory sheet, you can maybe use the following formula for cell C3 in the Sales sheet:

=IFERROR(INDEX(tblInventory[Code], MATCH(1, (tblInventory[Product]=B2)*(tblInventory[Name]=B3)*(tblInventory[Quantity]>0), 0)), "")

Upper formula is untested

Make sure to replace tblInventory with the actual table name in your Inventory sheet.

Here's how the formula works:

1. INDEX(tblInventory[Code], ...) - This function returns the value in the Code column of the tblInventory table.
2. MATCH(1, ...) - This function finds the position of the first "1" in the specified array.
3. (tblInventory[Product]=B2)*(tblInventory[Name]=B3)*(tblInventory[Quantity]>0) - This is an array formula that checks for multiple criteria:
• Checks if the Product column in Inventory matches the value in B2 (Water Tank in this case).
• Checks if the Name column in Inventory matches the value in B3 (Bash in this case).
• Checks if the Quantity in Inventory is greater than 0.
4. IFERROR(..., "") - This function handles errors. If no match is found, it returns an empty string (""), indicating that there is no stock available.

Now, when you copy this formula down in column C of the Sales sheet, it will automatically pick the correct Code for each sale, considering the specified criteria and quantity.

Remember to adjust the table name and column references based on your actual data layout. Additionally, if you are entering this formula into an Excel table, you might need to use structured references. The text, steps and formula was created with the help of AI. If the Steps/formula don’t works please provide more information.

 Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

[#3]

@NikolinoDE 

Thanks for Replying,

I am attaching photos see them sir,

I changed some of references as you mentioned.

But you can see C-PO-1 is not changed when stock for C-PO-1 ends in Inventory.

It should become C-PO-2 after I sale 200 units.

Rest is in your hands

[#4]

@NikolinoDE 

 

   

[#5]

I am using windows 10 on Hp Laptop
Ms excel web <Only because choosecols function didn't work on desktop version+
Max usage will be on android mobiles.

[#6]

@NikolinoDE Here is original file link. You will see there I highlighted areas of problem.

LCD Manager 2024.xlsx

[#7]

@MansoorAli 

I'm currently unable to directly access external links or files. However, I try to guide you on how to implement the formula in your provided scenario.

If you are using Excel Web and Android mobiles, and the CHOOSECOLS function is not supported, you can try using a combination of INDEX, MATCH, and FILTER functions.

Assuming your Sales table starts from cell A1 in the Sales sheet, and your Inventory table starts from cell A1 in the Inventory sheet, you can use a formula similar to the following:

=IFERROR(INDEX(tblInventory[Code], MATCH(1, (tblInventory[Product]=B2)*(tblInventory[Name]=B3)*(tblInventory[Quantity]>0), 0)), "")

Remember to replace tblInventory with the actual table name in your Inventory sheet.

[#8]

Sir, I sent link so you can see the actual problem easily. B2 and b3 you mentiond in vertical but it is b2 and c3, which is horizantal. With xlook or sumproduct. I reach codes but here i dont get codes for current stock if previous stock ends. As was the problem from very beginning of this.
Thanks