CODEWITHSUNDEEP
excelformula
natiz
natiz

Reputation: 573

Excel: Matching the closest date from range of dates

I'm trying to display the header of the column that its cell that contains a date, is the closest (out of 3 other cells with dates, B2:D2) to the a specific cell (A2)

So here's my sheet:

       A           B          C          D
1 |    T1    |     T2    |    T3    |    T4    |
2 | 5/1/2013 | 5/31/2013 | 2/5/2013 | 3/2/2013 |

A2 = a date.
B2:D2 = 3 dates, can also be blank

I've tried using the following array formula that I found online:

=IF(COUNT($B2:$D2)>0,INDEX($B$1:$D$1,MATCH(MIN(ABS($B2:$D2-$A2)),ABS($B2:$D2-$A2),0)),"")

But it sometimes when there is only one or two dates, it gives me the wrong header. I would also like to avoid using array formulas, but couldn't convert it to a regular one.

Would highly appreciate assistance on this matter

Upvotes: 1

Views: 7927

Answers (3)

lori_m
lori_m

Reputation: 5567

For a non-array alternative, you could try:

=LOOKUP(2,1/FREQUENCY(0,ABS(B2:D2-A2)),B$1:D$1)

in place of the INDEX(...) part of the formula.

Upvotes: 1

natiz
natiz

Reputation: 573

Okay so the answer was fairly simple.

I went back to the sheet to investigate the scenarios in which this happens. It seems that when A2 is blank (= no date to make the comparison) it just gives out the only date available from B2:D2.

By simply adding another condition to the IF statemented that would work only if A2 has a value resolved the issue. So we're checking if both A2 and B2:D2 has values in them (with B2:D2 needs to be at least one:

=IF(AND(COUNT($B2:$D2)>0,$A2>0),......)

and here is the full formula

=IF(AND(COUNT($B2:$D2)>0,$A2>0),INDEX($B$1:$D$1,MATCH(MIN(ABS($B2:$D2-$A2)),ABS($B2:$D2-$A2),0)),"")

Thanks for making me go back to the investigate! :D

Upvotes: 1

Wild138
Wild138

Reputation: 573

Can you give an instance were the array formula you are using isn't working ? It worked for the cases I tried.

You can avoid using the array formula if you are prepared to have another row with the difference in the dates and your date cell A2.

Push your rows down one row, so the headings are on row 2 and the dates on row 3 and enter the following formula across row 1, starting at cell B1

=ABS(B3-$A$3)

Drag this across and enter the following HLOOKUP to get your header row

=HLOOKUP(MIN(B1:D1),B1:D3,2,FALSE)

if you can't move your rows down for some reason you can use a combination of MATCH and INDEX (which is slower) . If you were to put the difference function =ABS(B2-$A$2) across row 7 the formula would be

=INDEX(B1:D1,1,MATCH(MIN(B7:D7),B7:D7,0))

Upvotes: 2

Related Questions