How do I calculate closest past & closest future dates base on multiple criteria

Hi,

Please see attached file.

This is a table made up of a list of Part Numbers (column A) and a corresponding delivery date (column B). In column C I have entered the following formula (after research online and in this forum):

=INDEX($B$4:$B$778,MATCH(MIN(IF($A$4:$A$778=$A4,ABS($B$4:$B$778-$B$1))),IF($A$4:$A$778=$A4,ABS($B$4:$B$778-$B$1)),0))

I also use CTRL+SHIFT+ENTER when completing

I know my way around excel a little, but am certainly no guru. It is my belief that this formula is providing me with the closest delivery date prior to the date I have specified in cell B1. However for some reason a few dates seem to produce #VALUE! but I am unsure why?
I am not too fussed at this point about the results in column C where there is no delivery date supplied in column B

I have 2 main queries that I am seeking your help with:

(1) Is the formula in column C giving me the result I think it is (closest past date)? And if so, is it structured correctly or is it over complicated?

(2) How can this formula be amended to provide me with the closest future date (to be entered separately in column D for example)

Any help or feedback would be greatly appreciated!

Many thanks

I can see now that this formula is not actually doing what I wanted it to.

In all cases, the date I want to return last prior date and next future date is against cell B2 (16/06/2017). However the value being returned against Part Number NP50232 (value 04/07/2017) is clearly not prior to 16/06/2017!

If there is no prior or future date, just returning a blank cell would be fine in this particular case.

This is great, exactly what I was after!

One additional query - I have now added the Delivery Qty in column C (see attached updated file "Book1.2.xls") which I would like to link to each part number and respective recent/future delivery date. I thought one way of doing this could be to concatenate Part Number & Date and do some kind of lookup, however on occasion there are Part Numbers that have more than one delivery on the same date, plus a lookup would only pickup the first record it matches with.

Is there a way to pull the Qty across from column C and match it to the relevant most recent and next future dates, as calculated in your formula solution below?

I suspect maybe some kind of index/match is required.

Hi, yes it only pulls the first value.
For example, Part Number RPUKSN10591 has 2 deliveries on 22/06 for two different quantities (17,430 & 6,608), however that formula picks up the 17,430 figure for both records.

Good point! Thinking about it, I'd actually need to SUM the total delivery quantity on the same day for the same part number. So in the example of Part Number RPUKSN10591 I'd want the formula to show 24,038 (17,430 + 6,608).

It wouldn't matter too much if the 24,038 showed in both records (i.e. the 2 rows for the 2 deliveries on the same day) as if I reference this PNo / Del date / quantity in another sheet or a pivot for example, I could get round that by looking up the first value for each part number of by summarising by MAX or something in a pivot.

I think it does yes! Can't find an example where it doesn't.

Special-K, thank you for your speedy help with this query today. This element is part of a much larger project and this issue with past/future dates relevant to unique part numbers had proved to be my stumbling block... until now!

Very much appreciate all your feedback and solutions.

Hi Special-K,

Just a shoutout for the huge help. i had a similar struggle and i could amend this formula to work perfectly to get what i need.

Small typo i believe was in this formula: =IF(MIN(IF((A$4:A$778=A4)*(B$4:B$778>B4),B$4:B$778))=0,"",MAX(IF((A$4:A$778=A4)*(B$4:B$778>B4),B$4:B$778)))
Both should have been MIN to display the nearest future date. With the as-is, it would give me always the last possible future date.

Thanks again, you saved me a big headache, even 6 years later