How to find closest date in a column with conditions on other columns... I have

Question

How to find closest date in a column with conditions on other columns...

I have a excel sheet where each line have a product from different suppliers like following

Col A Col B Col S

Serial Number Supplier Date

R2 XX S1 D1

R3 YY S2 D2

R4 XY S3 D2

R8 XX S1 D3

Each line have much more info but I dont think its relevant for question,

I am using a construct like =IF(S2=MAX(INDEX(($A$2:$A$8=A2)*($B$2:$B$8=B2)*($S$2:$S$8),,)),"Yes","No") to find out what the newest price is for a certain product from a certain supplier. In above case I would have the formula in Column T giving No, Yes, Yes, Yes for Row 2 to 8.

This works well :-)

Question is, how do I do if I want to get a price closest to a date I decide???

Lets say I want the price in the list for a certain product from a certain supplier that is closestt tto 01-01-2014.

I would like to keep same kind of construct if possible,

Explanation / Answer

Enter your date of interest in cell T1, then in T2 use this array-entered formula (enter using Ctrl-Shift-Enter)

=IF(ABS(IF($A$2:$A$8=A2,IF($B$2:$B$8=B2,$T$1-S2)))=MIN(ABS(IF($A$2:$A$8=A2,IF($B$2:$B$8=B2,$T$1-$S$2:$S$8)))),"Yes","No")

It will return two "Yes" values if the date of interest is exactly between two dates - you can fix that to select the earlier or later date by using a fractional day offset like so, for preference for earlier dates:

=IF(ABS(IF($A$2:$A$8=A2,IF($B$2:$B$8=B2,$T$1-S2-0.1)))=MIN(ABS(IF($A$2:$A$8=A2,IF($B$2:$B$8=B2,$T$1-$S$2:$S$8-0.1)))),"Yes","No")

Or later dates

=IF(ABS(IF($A$2:$A$8=A2,IF($B$2:$B$8=B2,$T$1-S2+0.1)))=MIN(ABS(IF($A$2:$A$8=A2,IF($B$2:$B$8=B2,$T$1-$S$2:$S$8+0.1)))),"Yes","No")

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.