VLOOKUP or INDEX or whatever formula over 2 workbooks and multiple sheets with different ranges needed

[casexcel]

We are a large food service company for a university. Even though we're using Google Sheets for all of these, the formula will be the same.

We are currently using Google Sheets to create our recipes. We need to pull ingredients from multiple vendors in order to cost them out.

Costing Sheet:

Current formula in C14:

The formula works now because I only have 1 vendor and range. I tried adding another Importrange array formula but because they are different ranges and because vendors use the same item numbers, I cannot get it to work.

I need a formula in C14 that can look at the proper vendor tab based on what Vendor Code is entered.

Vendor Lists: Workbook name: Vendor order guides
Multiple Tabs:

Vendor 1 Tab (V1):

Vendor 2 Tab (V2):

Vendor 3 Tab (V3):

 

[casexcel]

Correction in sentence:

I need a formula in C14 that can look at the proper vendor tab based on what Vendor Code is entered and pull the description based on the item number entered