Vlookup – Picking up first date and then picking up second date if there is one.

Hi there,

This is my first ever post but I have used the forum to look at other people’s questions – they have been a lot of help so thanks to everyone out there. However, this time, I have not been able to find an answer to my problem so I am appealing for help.

I look after the inventory at a small company. We know how much stock we have in at any time and know roughly how many of each product we sell a week so we know when to order stock and when we will have a stock out etc. Recently, I have been building a sheet which looks at our inventory levels taking into account orders we have placed. My workbook has two sheets, one for “inventory levels” and one is “live orders”

Every product has a unique code (ie Product 1 – Small, Product 1 – Medium, ….., Product 2 – Small, Product 2 – Medium……) On the inventory levels sheet I have columns for “Order Volume”, “Order Status” “Warehouse arrival date – SEA freight” “Warehouse arrival date – Air Freight”, the data for which comes from the “live orders” sheet using a vlookup on the product code. This all works well BUT I have a problem when there is more than one live order outstanding for the same product code ie one order for Product 1 – Medium arriving on one date and another order for Product 1 – Medium which is arriving on a second date as the vlookup just picks up the first order.

On the inventory levels sheet I have created a “first order” section and a “second order” section. I have been searching for a way for the vlookup to put the first live order data in the First Order section under “Order Volume”, “Order Status” “Warehouse arrival date – SEA freight” “Warehouse arrival date – AIR Freight” and then IF there is a second live order then the data will go in the Second Order section.

Then, if there are two orders and the first one is received into the warehouse there would only be one live order so the data in the "first order" section will disappear and be replaced by the data that was in the "second order" section.

Sorry if this is not that clear – I have tried my best – but if it is not I have uploaded a file which should make it clear hopefully. As I say, if a product code only has one live order I’m fine but it all goes wrong when there is a second live order for the same thing!

Thanks

Cranston

Hi tigeravatar,

Thanks so much for your quick reply and sorry for my slow response (I was away for the weekend and blissfully had no BB signal!).

I have come into the office on Sunday night to have a look at this. I'm afraid that I have spent over 90 mins on it but can't seem to get your formula to work on my workbook. I'm reasonable at excel (I've been an accountant for 6 years) but this has proved to be beyond me. I'm also the one people in the office look to for help with excel so I won't get help from them.

I know this is slightly cheeky (and maybe against rules - if so obviously don't do it and please let me know) but could you do the formula for the first two columns and post the formulas in there please? ie cell A has formula "BCD" and cell E had formula "FGH" in it.

This would be such a help so if you could tigeravatar I would be hugely grateful.

Kind regards

Cranston

Or if anyone else can that would be brilliant. It should be stated that I will probably not be able to help back as I'm very much at the bottom of the excel learning curve but it would be a great help to me if you could.

tigeravatar,

Thank you SO much! That works perfectly! I think I understand it too which is a great help to me and my colleagues. If I could I would be you a beer.

This forum (and others like it) are so helpful but it seems to me that they are maybe underused and not valued nearly highly enough ie this will help my company hugely.

Thanks again!

Cranston