Forum Discussion
Lookup with multiple criteria between 2 sheets
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
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:
- INDEX(tblInventory[Code], ...) - This function returns the value in the Code column of the tblInventory table.
- MATCH(1, ...) - This function finds the position of the first "1" in the specified array.
- (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.
- 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.
NikolinoDE Here is original file link. You will see there I highlighted areas of problem.
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.
- 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.
