Look up list values between two values; list row contents

[ACurtis802]

Hi everyone,

Here is what I am trying to achieve:

Sheet2 contains various data, including one column of dates.

What I would like to do is populate Sheet1 with functions that search Sheet2 for date values between two separate date values on Sheet1 (cells F1 and G1). When a match is found on Sheet2, row contents from Sheet2 should populate on Sheet1.

In addition, I would like the functions only to match listings in which the value in column E on Sheet2 is greater than zero.

I have attached a sample workbook that illustrates the structure of the worksheets and the desired results. Looking forward to finding a good solution.

Thanks and best regards,
ACurtis802

[DonkeyOte]

See attached, I would strongly advise you use Helpers as illustrated to avoid need for array formulae -- arrays will slow your file.

(You might also want to consider using a Pivot Table)

EDIT: 0753 UK Time - Attachment updated to illustrate also PT approach

[ACurtis802]

Thanks, DonkeyOte.

Would you mind demonstrating what the array formulae would look like? I have tried my hand at it a few times, but have hit a snag in specifying that the date values must be greater than or equal to the earlier date limit, less than or equal to the later date limit, and the dollar amount greater than zero.

This is a small workbook, so I am not very concerned about the speed impact.

Thanks and best regards,
ACurtis802

[Steve R]

See attached workbook

[DonkeyOte]

IMO using Arrays when they can be avoiding is not good a habit to develop -- better to develop approaches that permit you to avoid them.

For array approach see attached - sheet Array.
Note use of Sumproduct in H1... ideally you would store the SMALL array in a separate column and use traditional non-array INDEX in A:E each referencing the column containing the SMALL array value ... else you are repeating the same calc over and over (this is the reason for the Sumproduct in H1) ... this is basic XL error... never repeat a calc over and over where it can be avoided... simply store the calc in one location and have all others requiring the result reference that cell....

eg if you entered the following:

H2: 
=IF(ROWS(H$2:H2)>$H$1,0,SMALL(IF((Original!$D$2:$D$1000>=$F$1)*(Original!$D$2:$D$1000<=$G$1)*(Original!$E$2:$E$1000>0),ROW(E$2:E$1000)),ROWS(E$2:E2)))
entered as array with CTRL + SHIFT + ENTER
copied down say to H20

Then your other cells can merely reference H for row number to be used in the INDEX they need not repeatedly conduct the SMALL array test, eg:

A2:
=IF($H2,INDEX(Original!A$1:A$1000,$H2),"")
copied across entire matrix, eg A2:E20

Thus you are using only 19 array formulae (H2:H19) as opposed to 95 (A2:E19) ... further given the fact that of those 95 arrays 76 of them (ie those in cols B to E) are simply replicating what has already been calculated in A (ie the row number is constant for A:E for any given row) -- adopting a different approach is/should be a no-brainer for any developer.

Using helpers as outlined previously allows you to dispense with arrays altogether.

[ACurtis802]

Hi DonkeyOte and Steve R,

Thank you for your help. These solutions give me a number of workable options.

Best regards,
ACurtis802