Formula to Capture Only Dates With Corresponding Numbers (no empty cells)

use this array formula in E3, copied down:

=IFERROR(INDEX(B:B,SMALL(IF($C$3:$C$33>0,ROW($C$3:$C$33)),ROWS($E$3:E3))),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Yes. All you need to do is add another IF condition into the formula. Set here to take date from E2...


=IFERROR(INDEX(B:B,SMALL(IF($C$3:$C$33>0,IF($B$3:$B$33>=$E$2,ROW($C$3:$C$33))),ROWS($E$3:E3))),"")

again, entered as an array.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

In your posted sheet, are you looking at ALL columns (I to N) or ONLY the one specified by C2??

Yep.

No problem. I had to move your manually inputted start date. With it JUST above your results, it would have EXCLUDED any results on the start date. array-netered formula in C8, copied down:

=IFERROR(INDEX(H:H,SMALL(IF(($I$6:$N$39<>"")*COLUMN($I$6:$N$39)*($H$6:$H$39>=$C$6)*(COUNTIF($C$7:C7,$H$6:$H$39)=0),ROW($H$6:$H$39)),1)),"")

How does this look?

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

In your first post describing the new request (Post 7) you wanted the dates returned to be greater than or equal to the manually inputted date. To do that, unless, you add some very awkward phrasing, it ABSOLUTELY need to move the selection cell to any cell OTHER than the one above the results. That is what I did. You have now changed your requirement to be greater than (and NOT greater than or equal to).

That may be what you want, but logically it looks VERY strange. If you enter March 1st in c7, your first result is March 3rd, EVEN THOUGH there are matching results for March 1st.... Think very carefully about this... Will you / the intended users of the sheet ALWAYS be aware that they need to put in the date of the day before the date when they want the results to start. It is not logical and is really not a good plan!!!

You have been warned....

How does it work...

One bit can be omitted. The column (XX:yy) bit.

=IFERROR(INDEX(H:H,SMALL(IF(($I$6:$N$39<>"")*($H$6:$H$39>$C$7)*(COUNTIF($C$7:C7,$H$6:$H$39)=0),ROW($H$6:$H$39)),1)),"")

For every non-blank cell in the 2D array, return TRUE, otherwise FALSE

Return T/F for dates > manual day-before-the-desired-start-date (AWFUL!!!) and multiply, returns 1 (T) and 0 (F)

Return only those results that do not appear above the cell in which the formula is in (this is where you need your awful "start date is the day before you REALLY want the results to start from" bit) - prevents repitition of multiple matching dates

Return the row number IF all the previous criteria are TRUE, i.e. have a non-zero value. Returns row number or FALSE.

The remainder returns the first instance of the date from column H that matches all the above criteria. On the next (and subsequent) row(s), the result above and the other instances of the first date are excluded by the COUNTIF bit.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Nothing has been attached...

The first couple of things that I've tried have failed miserably. Can you try this as a workaround:

=IFERROR(1/(1/INDEX($I$6:$N$39,MATCH($C6,$H$6:$H$39,0),MATCH(D$5,$I$5:$N$5,0))),NA())

in D6, copied across and down. This converts all 0s to #N/A errors, which Excel will ignore in drawing a chart. If they're hard on the eyes, you can always use conditional formatting to change their text colour to white. Still there, but you can't see them....

In C7, an array formula:

=IFERROR(INDEX(H:H,SMALL(IF(((INDEX($I$6:$N$39,,MATCH($D$5,$I$5:$N$5,0))<>"")+(INDEX($I$6:$N$39,,MATCH($E$5,$I$5:$N$5,0))<>"")+(INDEX($I$6:$N$39,,MATCH($F$5,$I$5:$N$5,0))<>""))*($H$6:$H$39>$C$6)*(COUNTIF($C$6:C6,$H$6:$H$39)=0),ROW($H$6:$H$39)),1)),"")

I was being stupid yesterday... I got my brackets in the wrong places and I got my ANDs and ORs muddled up!!

If you want to suppress the zeros in your chart, use this for the lookups:
=IFERROR(1/(1/VLOOKUP($C6,$H$5:$N$39,MATCH(D$5,$H$5:$N$5,0),FALSE)),NA())

and then use CF to make them invisible, by changing their text colour to white.

Any time...You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.