Index / Match - Min Date

[mmargas]

Hi There !

New to the forum, but I've been looking at different forum threads in the past. It's my first post and I was hoping someone had some insight into a problem I have with a match index array formula.

My requirement is to have a dynamic formula that pulls back the minimum date based on a category. I am looking to run some statistics for a set of webinars that people respond to within 24 hours or more. I am trying to pull the first / min date of the webinar based on the webinar.

For example if I have 40 responses for a webinar ranging from 07/27/2017 to 08/03/2017. I am trying to return 07/27/2017 for each of the 40 responses on that webinar.

I have created an array formula that provides me with the Unique webinars that are entered into the sheet.

=IFERROR(INDEX(Webinars, MATCH(0,COUNTIF($N$1:N1, Webinars), 0)),"ERROR") -- Webinars is the range named for $B$1:$B$3000

From there I have extracted the date and time from the survey time column and have worked towards 'Date of Webinar' formula.

In an attempt to break it down, I have started a sub section starting in column P with sample data that I tried to mirror the full data set.

This is the current formula that I was able to come up with. It works for some scenarios but not all.

=INDEX($T$5:$T$14,MATCH(W5,$S$5:$S$14,0),MATCH($P$5:P5,$P$5:$P$14,0))

[sandy666]

You've Ex2013 so use xlsx extension not xls

[mmargas]

Hi Sandy,

Thanks for the response. I've added the Excel workbook version.

[Zer0Cool]

If I am understanding correctly you want to return the earliest date for a given webinar?

If so I created a new column in O, you can place it anywhere you like. Array enter using Ctrl+Shift+Enter, then fill down the column.

Please Login or Register  to view this content.

[63falcondude]

Please Login or Register  to view this content.

Is there a benefit of this over {=MIN(IF($C$2:$C$225=C2,$F$2:$F$225))}?

[sandy666]

If I understood well,
see attached file

[Zer0Cool]

Is there a benefit of this over {=MIN(IF($C$2:$C$225=C2,$F$2:$F$225))}?

Nope that would work as well. The only advantage of SMALL would be making it easy to change the nth lowest value to return, which the OP likely doesnt need for this application.

[mmargas]

The formula =SMALL(IF($C$2:$C$225=C2,$F$2:$F$225),1) pulls back the minimum date for the whole in F no?

I would expect the following all rows per Webinar:

HCCs and CDI... to be 07/27/2017
Master Oasis to be 07/27/2017
Reduce Denails to be 07/12/2017

Am I missing something?

[mmargas]

The formula {=MIN(IF($C$2:$C$225=C2,$F$2:$F$225))} works but the small did not.

[63falcondude]

MIN(IF(...)) and SMALL(IF(...),1) do the same thing. Both have to be entered using Ctrl Shift Enter instead of just Enter.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[mmargas]

Thanks that worked. I have a follow-up question. Now that I've found the first date of the webinar, I am trying to concat that date (column H) with a time of 12:00:PM. However, when I go to concat it, it still takes the time from the date format even though I've adjusted the formatting of the cell.

In Column J I am trying to concat H and I within a date format, so the net result to show like 07/27/2017 12:00. Then in L, I am trying to represent the time difference to see if it is within 24 hours.

[63falcondude]

Try this:

J2 =INT(H2)+I2

Formatting does not change the value of the cell (i.e. the time is still in there even though it isn't showing).

[63falcondude]

Better yet, you can get rid of most of your helper columns by putting this formula into F2:

=INT(MIN(IF(Table1[Webinar Name]=C2,Table1[Survey Time & Date])))+TIME(12,0,0) Ctrl Shift Enter

That will return the date and time of each webinar (you will have to adjust the formatting to show the time).

[mmargas]

That formula only returns the minimum date through all of column E, not the minimum by webinar.

[mmargas]

Martin needs to press CRTL+SHIFT+ENT - Silly goose.

Thanks!

[63falcondude]

Great, happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.