How to do a Vlookup in a conditional situation

Hello y'all,

Is there anyone who know a way to import data to cells with vlookup depends on number of inventory stock. To be more precise I want to show examples lets say I have 2 different excel workbooks.

Table 1 shows product barcodes and which warehouses to order them.

Table 2 shows product barcodes, stock numbers in different warehouses.

I want to use vlookup in "Which warehouse should I order the product from?" column but my wish is when stock run out from one certain warehouse it has to automatically shift to next warehouse which has a stock.

I explained more detailed in excel file in the attachment.

I would be really appreciate if someone help me about this I haven't find anything as solution so I am thinking its not doable in excel maybe I need some sort of programming.

Thanks!

Thank you

Oh I see I didnt know it, I just updated

menem, thanks for the reply but when I type the formula in cell it says, "there is a problem with this formula)

samba_ravi, thanks for the reply. I think that but unfortunely helper column doesnt help me

Why can you not use a helper column ?

JohnTopley, thanks for the reply. As far as I know helper column needs other data to combine with 2+ columns. So in my case I have only one product barcode and needs to take number of stocks from different workbook. I have tried before and couldn't find a way. But if you got a solution with it, I would be happy to hear

menem, after opening your excel I tried the same formula and it worked! Thats awesome, I will try with big data excel later, it seems it will work. I really appreciate your help, you save me

samba_ravi, This is also what I was looking for exactly, I appreciated your time and help as well. Really thank you!