Retrieving multiple values that are greater than a certain value

[spillon]

Hi,

I need to pull a number of names where the dates in a corresponding column are greater than today.

E.g.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Tom[/TD]
[TD]5/4/17[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Mike[/TD]
[TD]30/6/18[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jim[/TD]
[TD]3/3/16[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Peter[/TD]
[TD]8/1/17[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Steven[/TD]
[TD]9/11/13[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Melanie[/TD]
[TD]16/12/17[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Joe[/TD]
[TD]19/10/14[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Stephanie[/TD]
[TD]25/9/17[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Michelle[/TD]
[TD]29/2/15[/TD]
[/TR]
</tbody>[/TABLE]

I know the following array formula:
=IF(ISERROR(INDEX($A$1:$B$10,SMALL(IF($B:$B=X,ROW($B:$B)),ROW(1:1)),1)),"",INDEX($A$1:$B$10,SMALL(IF($B:$B=X,ROW($B:$B)),ROW(1:1)),1))

However for some reason it does not appear to work with greater than (>) e.g.
=IF(ISERROR(INDEX($A$1:$B$10,SMALL(IF($B:$B>today(),ROW($B:$B)),ROW(1:1)),1)),"",INDEX($A$1:$B$10,SMALL(IF($B:$B>today(),ROW($B:$B)),ROW(1:1)),1))

Is anyone able to help with this?

Thanks.

 

[jorismoerings]

Hi,

Did you enter it as an array formula? So confirmed it with Ctrl-Shft-Enter?

 

[Aladin Akyurek]

[TABLE="class: grid, width: 298"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD="align: right"]4/5/2017[/TD]
[TD][/TD]
[TD]Mike[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD="align: right"]6/30/2018[/TD]
[TD][/TD]
[TD]Melanie[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD="align: right"]3/3/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD="align: right"]1/8/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steven[/TD]
[TD="align: right"]11/9/2013[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Melanie[/TD]
[TD="align: right"]12/16/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD="align: right"]10/19/2014[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stephanie[/TD]
[TD="align: right"]9/25/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Michelle[/TD]
[TD="align: right"]2/28/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In D1 enter:

=COUNTIFS(B2:B10,">"&TODAY())

In D2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($D$2:D2)>$D$1,"",INDEX($A$2:$A$10,SMALL(IF($B$2:$B$10>TODAY(),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($D$2:D2))))

 

[ahmedmirza]

Hi

Did you try using advanced filter?

Try the following:

Say the data in your example is placed on cells A1:B10
- Insert the following in cell E2.... =">"&TEXT(TODAY(),"dd/mm/yyyy")
- Click on advanced filter
- click Copy to another location
- List range: $A$1:$B$10
- Criteria range $E$1:$E$2
- Copy to range $A$15:$B$15

I believe the above is simple.

HTH

Ahmed Mirza

 

[ahmedmirza]

I missed to mention... step 1, Insert the heading Date in cell E1