finding closest date after a referenced date for a specific item from an array

**uptickdk** · 05-14-2014, 05:09 PM

I have 2 separate arrays of 2 columns with data starting in row 6 and text in columns in between the 2 arrays. The first array has Item names and buy dates columns K & L. The second array has Item names and sold date columns R & S.

I need to find the date each Item that was bought was sold. There are multiple buys of the same item so the sold date must be the first date after to purchase date for that item. I tried vlookup and match but couldn't get it to work. I keep trying to use an array but am struggling with it.

Thanks in advance,
Doug

**cbatrody** · 05-15-2014, 02:01 AM

Hi Doug,

Please post a sample template of your workbook here using "Go Advanced" option.

**uptickdk** · 05-15-2014, 10:06 AM

I have attached a spreadsheet and am modifying what is needed.

The spreadsheet has 2 2 column arrays, with an item number and a date. The first array has the items and buy dates and the second has the items and sell dates. The items repeat themselves and there can be more then one of the same items at the same time, which is where I have the problem. When there are more than 1 of the same items and I look up the items sell date if it is the 2nd, 3rd, 4th… it picks the first date as the sell date for all of them. I need the formula to pick the next remaining date using the first in first out method.
Example if there are 4 "item b"’s that were purchased at different dates when the second one is sold it needs to find the second oldest sell date (as the oldest sell date was used when the first item b was sold) and so on through the Nth time. see highlighted cells for the error.
I have tried 3 different arrays and match combos see try 1,2 and 3, but can’t seem to get the multiple item issue resolved.

Any thought are appreciated.