VLOOKUP, INDEX, MATCH, and CSE Formulas combined

[NicholasE]

Hello All,

I've been digging through the threads and cannot quite seem to find exactly what I am looking for. I have a 5 tab inventory system. Each tab contains vendors, items, on hand counts, and order qty (although, these pieces of information are not necessarily in the same columns in each tab). I want to create a 7th tab that will populate and order sheet. I want this sheet to search all 5 tabs for vendor X, determine if order qty is >0, and return item name. I am assuming that vendor x will have more than one item that needs to be ordered so I need this formula to populate and entire table as follows

Vendor X
[TABLE="width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Order[/TD]
[/TR]
[TR]
[TD]#1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]#2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]#3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]#4[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]


Once I have the equation that will populate the item numbers based on Vendor x and order quantity >0 I should be able to handle the VLOOKUP equation that will return the Order qty based on Item #.

Thanks in advance for the help

 

[James006]

Hello,

My recommendation is to visit XORLX 's great site ... if you are looking for a solution based on advanced formulas ...

https://excelxor.com/category/multiple-worksheets/

Otherwise, a macro can also handle this issue ...

Hope this will help