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:

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.41	2.42	2.39	2.54	2.62	2.63	2.61	2.64	2.72	2.79	2.92	3.03
########	2.31	2.33	2.37	2.52	2.7	2.8	2.83	2.84	2.92	3.01	3.19	3.3
########	2.2	2.26	2.34	2.49	2.69	2.87	2.93	2.98	3.07	3.15	3.3	3.39
########	2.12		2.19	2.36	2.59	2.81	2.88	2.94	3.01	3.05	3.13	3.19
########	1.95		2.11	2.28	2.46	2.62	2.7	2.74	2.81	2.86	2.95	3.02
########	1.94		2.03	2.21	2.44	2.67	2.77	2.85	2.92	2.96	3.03	3.08
########	1.77		1.93	2.11	2.33	2.52	2.63	2.73	2.81	2.85	2.91	2.98

<tbody>
</tbody>

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]

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
Quarter	Percent Delinquent 90 Days or More	Percent Delinquent 30 or 60 Days
2008 Quarter 1	2.1	3.1	2009 Quarter 2
2008 Quarter 2	2.4	2.8	2009 Quarter 2
2008 Quarter 3	2.5	2.8	2009 Quarter 2
2008 Quarter 4	3.0	3.3	2009 Quarter 2
2009 Quarter 1	3.7	3.8	2009 Quarter 2
2009 Quarter 2	4.3	3.3	2009 Quarter 2
2009 Quarter 3	4.9	3.3	2009 Quarter 2
2009 Quarter 4	5.4	3.5
2010 Quarter 1	5.9	3.6
2010 Quarter 2	6.0	3.1
...	...	...

<tbody>
</tbody>

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