Lookup with multiple criteria between 2 sheets

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.