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)

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

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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