Array formula needs a condition added to it

[ReturningHack]

Hi there, thanks for clicking.

Preamble

I used to be a bit of a power user with Excel, although somehow I don't remember needing to create an array formula until now. And what's worse is I've been in a new job for a few years and don't remember a lot of the Excel skills I used to have!

The problem

I've replicated an array formula that works great. I can't post links because I'm new otherwise I'd reference where I got it from but I guess that doesn't matter. It's a formula to list some rows of data on a dashboard sheet where the date row is between specific dates.

But I also need to add something to the array, so as well as coping with specific dates, it only returns rows that meet another condition. In this case, only if the row's column titled 'Target' matches the Target value on the dashboard sheet should it return the row.

I tried inserting an IF function into the array, and it kinda works; it indeed will only return rows that meet the 'Target' condition. However, I clearly don't know what I'm doing because instead of a tidy array without gaps, my IF function would result in row gaps because it's not very clever. (I've removed the IF function from the attached). I can understand why it does this, just about, but not how to overcome it.

So I'm hoping some Excel guru could please take a look at this and let me know how to amend the array formula on the dashboard so it doesn't lose its current functionality but also only returns rows that match the 'Target' value. I hope this is clear.

Thank you!

[Richard Buttrey]

Have you considered a simple Pivot Table and a Slicer or three to help with filtering it. This will avoid Excel functions altogether

[ReturningHack]

Thanks for replying Richard. For my needs that won't be sufficient (I stripped the attachment down but the actual spreadsheet is more complex).

I'm confident this can be achieved with an amendment to the existing formula. After all, it already does the requirement with one condition (date range); I'm just looking at something to extend this to an additional condition.

Although admittedly I don't know how to do it!

[Richard Buttrey]

Hi,

As you say this is clearly a much simplified example and doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned. It's why we always prefer to see the production workbook itself.

Therefore upload your real workbook (or at least a cut down but truly representative copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

[kvsrinivasamurthy]

Try this
ARRAY formula in A4 and copied across.

Please Login or Register  to view this content.

[ReturningHack]

Edit: Ignore, just reviewing the latest reply!

[Fluff13]

If you have the filter function
=FILTER(Data!A2:C10,(Data!A2:A10>=A2)*(Data!A2:A10<=B2)*(Data!C2:C10=D2))

[ReturningHack]

Hi kvsrinivasamurthy,

Thanks for the attempt. It doesn't quite work; it works correctly if the Target value in D2 is 'All', but if the Target value in D2 is 'Some' then the array doesn't differentiate and returns rows with either Target value. I've ensured the formula is entered as an array.

***

And hi Fluff13, sadly my current version of O365 doesn't have the =FILTER function.

[Fluff13]

Ok, how about
=IFERROR(INDEX(Data!A$2:A$22,AGGREGATE(15,6,(ROW(Data!$A$2:$A$22)-ROW(Data!$A$2)+1)/(Data!$A$2:$A$22>=$A$2)/(Data!$A$2:$A$22<=$B$2)/(Data!$C$2:$C$22=$D$2),ROWS(A$5:A5))),"")

[ReturningHack]

Ok, how about
=IFERROR(INDEX(Data!A$2:A$22,AGGREGATE(15,6,(ROW(Data!$A$2:$A$22)-ROW(Data!$A$2)+1)/(Data!$A$2:$A$22>=$A$2)/(Data!$A$2:$A$22<=$B$2)/(Data!$C$2:$C$22=$D$2),ROWS(A$5:A5))),"")

This is it!! Thank you Fluff13.

Thanks everyone who helped.

[Fluff13]

Glad to help & thanks for the feedback.

[ReturningHack]

It's the least I could do. I work for a cash-strapped public service organisation, and the file this will be used for will help several people.