Problems with array formula to list rows of a spreadsheet

[chrisk67]

I am working on a spreadsheet to list people authorised to travel with IT abroad). I am trying to work on a section detailing those whose authority expires in the next 30 days

Column A is the unique identifier
Column B is the days to expiry of authority.
rows B4-B5000 will contain the data

My formula is in cells B5026-B5060. Once I identify the row involved columns C onwards can be a straightforward vlookup for the other details. I have done this before but gotten bogged down so any help would be appreciated.

My formula is
=IFERROR(INDEX(A:A,SMALL(IF($B$4:$B$5000<30,ROW($B$4:$B5000)),ROWS($4:4))),"") (as an array)

I was going to upload the sheet but i dont appear able to upload a copy

 

[Fluff]

Hi & welcome to MrExcel.
Try
=IFERROR(INDEX($A$4:$A$5000,SMALL(IF($B$4:$B$5000<30,ROW($B$4:$B$5000)-ROW($B$4)+1),ROWS($1:1))),"")

 

[pgc01]

Hi

The $B5000 is missing a $

 

[chrisk67]

Thanks for your help. I have got that section sorted but now found that it identifies dates in after the deadline has passed. Can you suggest how I can restrict it to 7 days past the deadline and the same 30 before. I tried playing with IFAND but got bogged down again.

 

[Fluff]

Can you show us a sample of the data in col B showing which should be included.
There are some add-ins available here which allow you to post sample data to the board https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[chrisk67]

Can you show us a sample of the data in col B showing which should be included.
There are some add-ins available here which allow you to post sample data to the board https://www.mrexcel.com/forum/about-board/508133-attachments.html

I struggle to install add ons as the machine restricts admin level work (dlo or install) so have copied the columns B-D-C etc below. I am trying to identify only IT within 30 days of the deadline (but dont mid going 7 days over)

[TABLE="width: 1142"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Days to Expiry[/TD]
[TD]Item (SP or Smart Phone)[/TD]
[TD]Device Owner (surname)[/TD]
[TD]Device Owner (forename)[/TD]
[TD]Device Owner (PID)[/TD]
[TD]Device ID[/TD]
[TD]Reference[/TD]
[TD]Outbound Date[/TD]
[TD]Return Date[/TD]
[TD]Ad Hoc (Y/N)[/TD]
[TD]6M/12M dispensation[/TD]
[TD]Expiry Date[/TD]
[/TR]
[TR]
[TD]-8[/TD]
[TD]Smart Phone[/TD]
[TD]Smith[/TD]
[TD]Bill[/TD]
[TD]1234567[/TD]
[TD]1234[/TD]
[TD]1111/19[/TD]
[TD="align: right"]01/01/2019[/TD]
[TD="align: right"]02/01/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]02/01/2019[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Surface Pro[/TD]
[TD]Jones[/TD]
[TD]Fred[/TD]
[TD]7654321[/TD]
[TD]12324[/TD]
[TD]1212/19[/TD]
[TD="align: right"]01/02/2019[/TD]
[TD="align: right"]11/01/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]11/01/2019[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]Smart Phone[/TD]
[TD]Jackson[/TD]
[TD]michael[/TD]
[TD]5353537[/TD]
[TD]987641[/TD]
[TD]1213/19[/TD]
[TD="align: right"]02/01/2019[/TD]
[TD="align: right"]04/02/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]04/02/2019[/TD]
[/TR]
[TR]
[TD]177[/TD]
[TD]Surface Pro[/TD]
[TD]ted[/TD]
[TD]father[/TD]
[TD]11111111[/TD]
[TD]87yyy[/TD]
[TD]666/19[/TD]
[TD="align: right"]06/01/2019[/TD]
[TD="align: right"]21/01/2019[/TD]
[TD] [/TD]
[TD]6 months[/TD]
[TD="align: right"]06/07/2019[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Smart Phone[/TD]
[TD]jones[/TD]
[TD]jack[/TD]
[TD]6565657[/TD]
[TD]rrr555[/TD]
[TD]5656/19[/TD]
[TD="align: right"]10/01/2019[/TD]
[TD="align: right"]16/01/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]16/01/2019[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Surface Pro[/TD]
[TD]statham[/TD]
[TD]jason[/TD]
[TD]9876543[/TD]
[TD]nnhnhh[/TD]
[TD]1234/19[/TD]
[TD="align: right"]10/01/2019[/TD]
[TD="align: right"]01/02/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]01/02/2019[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Ok, try
=IFERROR(INDEX($A$4:$A$5000,SMALL(IF(($B$4:$B$5000<30)*($B$4:$B$5000>-8),ROW($B$4:$B$5000)-ROW($B$4)+1),ROWS($1:1))),"")

 

[chrisk67]

that looks like it has done the trick, man thanks for your help on this

 

[Fluff]

Glad to help & thanks for the feedback