How to find all the cells in a particular column when other column meets a condition?

[rooigle]

Hello members of Mr Excel,

My puzzle presents as follows ...


I have a table that looks pretty much like the following in a sample version:


[TABLE="width: 832"]
<tbody>[TR]
[TD="width: 64"]Date[/TD]
[TD="width: 64"]1 MO[/TD]
[TD="width: 64"]2 MO[/TD]
[TD="width: 64"]3 MO[/TD]
[TD="width: 64"]6 MO[/TD]
[TD="width: 64"]1 YR[/TD]
[TD="width: 64"]2 YR[/TD]
[TD="width: 64"]3 YR[/TD]
[TD="width: 64"]5 YR[/TD]
[TD="width: 64"]7 YR[/TD]
[TD="width: 64"]10 YR[/TD]
[TD="width: 64"]20 YR[/TD]
[TD="width: 64"]30 YR[/TD]
[/TR]
[TR]
[TD="class: xl63"]########[/TD]
[TD]2.41[/TD]
[TD]2.42[/TD]
[TD]2.39[/TD]
[TD]2.54[/TD]
[TD]2.62[/TD]
[TD]2.63[/TD]
[TD]2.61[/TD]
[TD]2.64[/TD]
[TD]2.72[/TD]
[TD]2.79[/TD]
[TD]2.92[/TD]
[TD]3.03[/TD]
[/TR]
[TR]
[TD="class: xl63"]########[/TD]
[TD]2.31[/TD]
[TD]2.33[/TD]
[TD]2.37[/TD]
[TD]2.52[/TD]
[TD]2.7[/TD]
[TD]2.8[/TD]
[TD]2.83[/TD]
[TD]2.84[/TD]
[TD]2.92[/TD]
[TD]3.01[/TD]
[TD]3.19[/TD]
[TD]3.3[/TD]
[/TR]
[TR]
[TD="class: xl63"]########[/TD]
[TD]2.2[/TD]
[TD]2.26[/TD]
[TD]2.34[/TD]
[TD]2.49[/TD]
[TD]2.69[/TD]
[TD]2.87[/TD]
[TD]2.93[/TD]
[TD]2.98[/TD]
[TD]3.07[/TD]
[TD]3.15[/TD]
[TD]3.3[/TD]
[TD]3.39[/TD]
[/TR]
[TR]
[TD="class: xl63"]########[/TD]
[TD]2.12[/TD]
[TD][/TD]
[TD]2.19[/TD]
[TD]2.36[/TD]
[TD]2.59[/TD]
[TD]2.81[/TD]
[TD]2.88[/TD]
[TD]2.94[/TD]
[TD]3.01[/TD]
[TD]3.05[/TD]
[TD]3.13[/TD]
[TD]3.19[/TD]
[/TR]
[TR]
[TD="class: xl63"]########[/TD]
[TD]1.95[/TD]
[TD][/TD]
[TD]2.11[/TD]
[TD]2.28[/TD]
[TD]2.46[/TD]
[TD]2.62[/TD]
[TD]2.7[/TD]
[TD]2.74[/TD]
[TD]2.81[/TD]
[TD]2.86[/TD]
[TD]2.95[/TD]
[TD]3.02[/TD]
[/TR]
[TR]
[TD="class: xl63"]########[/TD]
[TD]1.94[/TD]
[TD][/TD]
[TD]2.03[/TD]
[TD]2.21[/TD]
[TD]2.44[/TD]
[TD]2.67[/TD]
[TD]2.77[/TD]
[TD]2.85[/TD]
[TD]2.92[/TD]
[TD]2.96[/TD]
[TD]3.03[/TD]
[TD]3.08[/TD]
[/TR]
[TR]
[TD="class: xl63"]########[/TD]
[TD]1.77[/TD]
[TD][/TD]
[TD]1.93[/TD]
[TD]2.11[/TD]
[TD]2.33[/TD]
[TD]2.52[/TD]
[TD]2.63[/TD]
[TD]2.73[/TD]
[TD]2.81[/TD]
[TD]2.85[/TD]
[TD]2.91[/TD]
[TD]2.98[/TD]
[/TR]
</tbody>[/TABLE]


Basically, I want two things:

1. To return in successive rows (single column) the dates that correspond to those raw wherein the value in column 5 YR was greater than that in column 7 YR.

2. (related to the previous) To return in a single cell, the count of the number of rows wherein (again) the value in column 5 YR was greater than that in column 7 YR.


I think that the idea it's simple but I cannot get my head around it. I have been trying VLOOKUP, INDEX, MATH, COUNTIF, etc, but I was unsuccessful to date.


Thank you for your help!

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Date	1 MO	2 MO	3 MO	6 MO	1 YR	2 YR	3 YR	5 YR	7 YR	10 YR	20 YR	30 YR
2	01/12/2018	2.41	2.42	2.39	2.54	2.62	2.63	2.61	2.64	2.72	2.79	2.92	3.03		03/12/2018	2
3	02/12/2018	2.31	2.33	2.37	2.52	2.7	2.8	2.83	2.84	2.92	3.01	3.19	3.3		06/12/2018
4	03/12/2018	2.2	2.26	2.34	2.49	2.69	2.87	2.93	3.9	3.07	3.15	3.3	3.39
5	04/12/2018	2.12		2.19	2.36	2.59	2.81	2.88	2.94	3.01	3.05	3.13	3.19
6	05/12/2018	1.95		2.11	2.28	2.46	2.62	2.7	2.74	2.81	2.86	2.95	3.02
7	06/12/2018	1.94		2.03	2.21	2.44	2.67	2.77	2.85	2.65	2.96	3.03	3.08
8	07/12/2018	1.77		1.93	2.11	2.33	2.52	2.63	2.73	2.81	2.85	2.91	2.98
Sheet4

Cell Formulas
Range	Formula
O2	{=IFERROR(INDEX($A$2:$A$8,SMALL(IF($I$2:$I$8>$J$2:$J$8,ROW($A$2:$A$8)-ROW($A$2)+1),ROWS($1:1))),"")}
P2	{=SUM(--(I2:I8>J2:J8))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[rooigle]

Dear Fluff,


First of all, thanks for your quick reply!

I have been able to make the second formula work (=SUM...) but the first one yields me the same date (the first date when the condition is met) in all the cells that I target the array formula to. Any idea of what may I be doing wrong?


Thank you for your help!

 

[Fluff]

Are your dates actual dates, rather than text values?
Also are they dates or date & time?

 

[rooigle]

They are text values of the following type:

Date
2008 Quarter 1
2008 Quarter 2
...

 

[Fluff]

What is the formula that you are using?

 

[rooigle]

[TABLE="width: 604"]
<tbody>[TR]
[TD="colspan: 2"]Just to be more specific I copy the table wherein I am using now, plus the formula.


Formula (using it properly as an array function):

{=IFERROR(INDEX($A$7:$A$49,SMALL(IF($B$7:$B$49>$C$7:$C$49,ROW($A$7:$A$49)-ROW($A$7)+1),ROWS($7:7))),"")}


Table (wherein "2008 Quarter 1" is in cell A7 and the table ends in A49):


NATIONAL DELINQUENCY RATES[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Quarter[/TD]
[TD]Percent Delinquent
90 Days or More[/TD]
[TD]Percent Delinquent
30 or 60 Days[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2008 Quarter 1[/TD]
[TD]2.1[/TD]
[TD]3.1[/TD]
[TD]2009 Quarter 2[/TD]
[/TR]
[TR]
[TD]2008 Quarter 2[/TD]
[TD]2.4[/TD]
[TD]2.8[/TD]
[TD]2009 Quarter 2[/TD]
[/TR]
[TR]
[TD]2008 Quarter 3[/TD]
[TD]2.5[/TD]
[TD]2.8[/TD]
[TD]2009 Quarter 2[/TD]
[/TR]
[TR]
[TD]2008 Quarter 4[/TD]
[TD]3.0[/TD]
[TD]3.3[/TD]
[TD]2009 Quarter 2[/TD]
[/TR]
[TR]
[TD]2009 Quarter 1[/TD]
[TD]3.7[/TD]
[TD]3.8[/TD]
[TD]2009 Quarter 2[/TD]
[/TR]
[TR]
[TD]2009 Quarter 2[/TD]
[TD]4.3[/TD]
[TD]3.3[/TD]
[TD]2009 Quarter 2[/TD]
[/TR]
[TR]
[TD]2009 Quarter 3[/TD]
[TD]4.9[/TD]
[TD]3.3[/TD]
[TD]2009 Quarter 2[/TD]
[/TR]
[TR]
[TD]2009 Quarter 4[/TD]
[TD]5.4[/TD]
[TD]3.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2010 Quarter 1[/TD]
[TD]5.9[/TD]
[TD]3.6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2010 Quarter 2[/TD]
[TD]6.0[/TD]
[TD]3.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The formula has been applied on the far-right column and, as you can see, it constantly renders the same output.

 

[Fluff]

Not sure why it's doing that as it works for me

Excel 2013/2016
	A	B	C	D
6	Quarter	Percent Delinquent 90 Days or More	Percent Delinquent 30 or 60 Days
7	2008 Quarter 2	2.4	2.8	2009 Quarter 2
8	2008 Quarter 3	2.5	2.8	2009 Quarter 3
9	2008 Quarter 4	3	3.3	2009 Quarter 4
10	2009 Quarter 1	3.7	3.8	2010 Quarter 1
11	2009 Quarter 2	4.3	3.3	2010 Quarter 2
12	2009 Quarter 3	4.9	3.3
13	2009 Quarter 4	5.4	3.5
14	2010 Quarter 1	5.9	3.6
15	2010 Quarter 2	6	3.1
Sheet4

Cell Formulas
Range	Formula
D7	{=IFERROR(INDEX($A$7:$A$49,SMALL(IF($B$7:$B$49>$C$7:$C$49,ROW($A$7:$A$49)-ROW($A$7)+1),ROWS($7:7))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[rooigle]

Right, I now know what I was doing wrong. I was using it over several rows at the same time thinking that I had to do so since it was an array. Apparently I just had to drag it down from the first row.

Your formula works impeccably. Thank you Fluff!

 

[Fluff]

Glad you sorted it out & thanks for the feedback