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