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.

	Column A	Column B
1	Name	Date
2	Tom	5/4/17
3	Mike	30/6/18
4	Jim	3/3/16
5	Peter	8/1/17
6	Steven	9/11/13
7	Melanie	16/12/17
8	Joe	19/10/14
9	Stephanie	25/9/17
10	Michelle	29/2/15

<tbody>
</tbody>

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]

Name	Date		2
Tom	4/5/2017		Mike
Mike	6/30/2018		Melanie
Jim	3/3/2016
Peter	1/8/2017
Steven	11/9/2013
Melanie	12/16/2017
Joe	10/19/2014
Stephanie	9/25/2017
Michelle	2/28/2015

<tbody>
</tbody>

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