SOLVED

How to do a Vlookup in a conditional situation

Copper Contributor

Hello everyone,

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, stock numbers in different warehouses.

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

I want to make vlookup in "align stocks" 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.

 

 

For example in attachment , regular vlookup only get first value from table 1 which is new york. But normally new york's stocks of 111 is finishes in 2nd one. 3rd 111 should be MOSCOW and 4th 111 should be PARIS.

Is there any way formula/macro to do this? I would be really appreciate. if someone help me out in this one. Thanks in advance.

 

 

6 Replies

@erpoh67 

 

Your best bet will be a pivot table:

Yea_So_0-1631858554010.png

 

cheers

@erpoh67 

 

Here is an approximate example.

With your permission, I recommend inserting a file for the next time (without sensitive data),

as well as information about the Excel version and operating system.

 

I would be happy to know if I could help.

 

NikolinoDE

 

Was the answer useful? Mark them as helpful!

This will help all forum participants.

thanks for the reply:) But Im usually working on hundreds of rows and data, in that case pivot table wont be as effective. I wish there was a vba formula for what I was looking for, cheers

@NikolinoDE Hello nikolino, thanks for the reply. My excel version is 2108 and I am using windows 10.

 

I explained more detailed in excel and Im putting it to attachment as you asked. 

 

I would be really glad if there is a code or formula for this. Thanks in advance!

@erpoh67 

Here's an example

Sorry for the delay, but unfortunately my free time is limited on the weekend :)).

 

I would be happy to know if I could help.

 

NikolinoDE

 

Was the answer useful? Mark them as helpful!

This will help all forum participants.

 

best response confirmed by allyreckerman (Microsoft)
Solution

@NikolinoDE Hello, I actually found my exact solution that what I was looking for. I'm putting in an attachment in case if anyone will have the same issue as me. Thank you for your help and time:)

 

Have a nice day

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@NikolinoDE Hello, I actually found my exact solution that what I was looking for. I'm putting in an attachment in case if anyone will have the same issue as me. Thank you for your help and time:)

 

Have a nice day

View solution in original post