dariuzthepole
Board Regular
- Joined
- Jul 23, 2008
- Messages
- 111
Hi,
Apologies for posting another Index/Match thread, but I think I've been through them all now and I'm still struggling. Index/Match is my Achilles Heel so apologies if this is straightforward.
I have a summary tab displaying stats on various sites (each site has its own tab). An example of the stats for the first site is below. Further sites are in columns D, E, F etc.
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Max Value[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Max Value (1st Date)[/TD]
[TD]01/01/2016 00:00[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Max Value (2nd Date)[/TD]
[TD]???
[/TD]
[/TR]
</tbody>[/TABLE]
Cell C5 has a straightforward MAX formula looking to column E on the SiteA tab.
In cell C6 I have the following array formula (from another thread) which works:
N.B. On the Site A tab, column A has the date/times and column E has the data I want to analyse.
However, I cannot get this to return the next date value in cell C7. I've tried to play around with the formula but so far it only returns the same value as C6.
Any help would be greatly appreciated!
Apologies for posting another Index/Match thread, but I think I've been through them all now and I'm still struggling. Index/Match is my Achilles Heel so apologies if this is straightforward.
I have a summary tab displaying stats on various sites (each site has its own tab). An example of the stats for the first site is below. Further sites are in columns D, E, F etc.
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Max Value[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Max Value (1st Date)[/TD]
[TD]01/01/2016 00:00[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Max Value (2nd Date)[/TD]
[TD]???
[/TD]
[/TR]
</tbody>[/TABLE]
Cell C5 has a straightforward MAX formula looking to column E on the SiteA tab.
In cell C6 I have the following array formula (from another thread) which works:
Code:
{=IFERROR(INDEX(SiteA!$A$2:$A$115855,SMALL(IF($C$5=SiteA!$E$2:$E$115855,ROW(SiteA!$E$2:$E$115855)-ROW(SiteA!$E$2)+1),COLUMNS($C$1:C$1))),"")}
N.B. On the Site A tab, column A has the date/times and column E has the data I want to analyse.
However, I cannot get this to return the next date value in cell C7. I've tried to play around with the formula but so far it only returns the same value as C6.
Any help would be greatly appreciated!