vlook up with additional conditions - please help!

[hayley b]

Hi
Hoping someone can help with below

I have a two sheet database

Sheet one shows in columns

Product Code, Product Description, Location, Stock Count

Sheet two shows in columns

Client, Qty, Product Code, Description and Location (location field currently empty)

I am planning to use the VLOOKUP function to populate the Location field in sheet two by looking up the common Product code on both sheets.

However where I have two or more locations for a Code how can I formulate that the total qty of product located when greater than the stock in a single location moves to the 2nd (or 3rd etc location)

For example
Sheet One Shows

APP1, APPLE WHITE, A1,5
APP1, APPLE WHITE, A2,5
APP1, APPLE WHITE, A3,5

Sheet Two Shows

JOHN, 1, APP1, APPLE WHITE, LOCATION BLANK
BOB, 1, APP1, APPLE WHITE, LOCATION BLANK
MARK, 6, APP1, APPLE WHITE, LOCATION BLANK

My current formula gives me the location for all rows (clients) as A1 despite only 3 being available for Mark and what I want to formulate is the result of

JOHN, 1, APP1, APPLE WHITE, A1
BOB,1, APP1, APPLE WHITE, A1
MARK, 6, APP1, APPLE WHITE, A1 AND A2 (and if i can show 3 and 3 for each location even better)

Thanks so much!
Hayley

[dilipandey]

Hi Hayley,

Welcome to the forum.

Would suggest you to upload a sample workbook to support your query. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[hayley b]

Hi
Thanks this is all new to me
I cannot see where the option is to attach a file?

Thanks
hayley

[abousetta]

Click on the "Go Advanced" button below the text box then on the paperclip (or the Manage Attachments button later down the page)

[hayley b]

Thanks
I think I have uploaded the file.

[hayley b]

Anyone any ideas on this please?

I am at a complete loss and will have to start processing manually if I cannot find a solution

Thanks!

[dilipandey]

Not getting it....
Could you enter the number manually as you wish and then I can try obtaining them using formulas...?

It appears that you need multiple lookup results.. but not sure.


Regards,
DILIPandey

<click on below 'star' if this helps>

[hayley b]

Thanks
I am sure I am not being very clear....
Please see this attachment

Sheet 1 - Is my Stock Listing
Sheet 2 - Are the customer Orders (showing v look up which does not take into account the qty of stock)
I have shown what I would like to see on sheet 3

What I am asking is if there is a formula I can use that looks up the product code using vlook up to give me a location but also considers the previous rows above the vlookup that also have the same code and so that once the qty number is greater than shown in stock qty it would move down to the next corresponding row and if the requirement is from two locations it shows from both.

Thanks Hayley

[dilipandey]

Hi Hayley

I am afraid.. I am still not getting it..

On sheet 3 -> a) what data would be already present in Sheet 3 ? b) then what data you need to obtain from Sheet 1 ? c) and using what logic?
Please highlight using colors what is already available and what need to be obtained using formulas .. sorry for inconvenience but I am trying to help Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>