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
Bookmarks