Struggling to Use Small(IF...)) formula to return multiple results

[trhyh]

Hello,

This is my first post here, I'm a little unsure of how I should best ask this question.

I'm trying to use a Small(IF(...)) Formula to return results. Currently I have a set of data in which there is a column indicating the date that it's due, given in (d/m/y) format. In a separate sheet I have a cell in which I populate the current date that the report is generated, ex) today is 8/15/2019(C1), and a few cells over I have the formula =month(C1) which gives me "8".

My formula I'm currently using to display the rows in which the month is "8" is give by:

{=SMALL(IF(MONTH('Alberta Planning Sheet Falcon'!$D$1:$D$29='In Month FSAs'!$F$1),ROW('Alberta Planning Sheet Falcon'!$D$1:$D$29)),ROW(1:1))},

I'd eventually like to wrap an index match and iferror formula around this formula to show the data I'd like to present, however I'm struggling to get any output and receiving an error.

I suspect that it might be due to data types? Where the MONTH(...) may be generating some sort of data type dissimilar to the value I'm trying to equate it to.

Please let me know if I can provide any more information, thank you for any assistance.

 

[Fluff]

If col D contains dates try
{=SMALL(IF(MONTH('Alberta Planning Sheet Falcon'!$D$1:$D$29)='In Month FSAs'!$F$1,'Alberta Planning Sheet Falcon'!$D$1:$D$29),ROWS($1:1))},

 

[DataBlake]

can you supply example data you're working with and expected results? also for what you want wrapped in index match and iferror.
this will help whoever helps you understand the issue better.

edit: except that fluff is the fastest person alive at solving excel problems. so i'm gunna stick my foot in my mouth now.

 

[trhyh]

Hi,

Thank you very much for helping so quickly-- I changed the formula to the one you suggested, I went from a #N/A error to #VALUE ! error.

Could I also ask you why you removed the first row function and changed the second function to "ROWS" with an absolute?

 

[trhyh]

I'm unsure how to edit-- so I hope it's ok to post another comment.

I was wondering if it's possible that function:

MONTH('Alberta Planning Sheet Falcon'!$D$1:$D$29='In Month FSAs'!$F$1)

Was causing errors, as it was trying to equate month function of an array of data, to a single cell of data. So I created a new column K,

In which I used =month(D1), and dragged it down to 29, and I changed the formula to be:

{=SMALL(IF('Alberta Planning Sheet Falcon'!$A$1:$K$29='In Month FSAs'!$F$1,ROW('Alberta Planning Sheet Falcon'!$A$1:$K$29)),ROW(1:1))},

But I'm receiving a #N/A error here as well.

 

[Fluff]

If the dates in col D are proper dates & not text the formula in post#2 should work
I've just done it all on one sheet (as it's easier)

Book1
	C	D	E	F	G	H
1	15/08/2019	01/08/2019		8
2		08/08/2019				01/08/2019
3		15/08/2019				08/08/2019
4		22/08/2019				15/08/2019
5		29/08/2019				22/08/2019
6		05/09/2019
7		12/09/2019
8		19/09/2019
9		26/09/2019
10		03/10/2019
11		10/10/2019
Data

Cell Formulas
Range	Formula
H2	=SMALL(IF(MONTH($D$1:$D$29)=$F$1,$D$1:$D$29),ROWS($1:1))

 

[trhyh]

If the dates in col D are proper dates & not text the formula in post#2 should work
I've just done it all on one sheet (as it's easier)

	C	D	E	F	G	H

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]15/08/2019[/TD]
[TD="align: right"]01/08/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]08/08/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]01/08/2019[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]15/08/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]08/08/2019[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]22/08/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]15/08/2019[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]29/08/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]22/08/2019[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]05/09/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12/09/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]19/09/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]26/09/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]03/10/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10/10/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Data​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=SMALL(IF(MONTH($D$1:$D$29)=$F$1,$D$1:$D$29),ROWS($1:1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hey, I've tried this as well and I'm unable to get any sort of output. Is there a way for me to share my file with you? I don't believe there is anything wrong with the dates, do they have to be in the same format? For example:

One is showing as 27-Sep-19 whereas the other one is 9/27/2019, as the MONTH(..) formula is being applied to it, I assumed it was appropriate to have the dates in different formats.

 

[Fluff]

You cannot upload files to the site, but you can upload to OneDrive, GoogleDrive, DropBox, mark the file for sharing & post the link to the thread.

If you select the column of dates & format as General, do you still see dates or numbers like 43678?

 

[trhyh]

Hey Fluff.

I was playing around with the table you made, I made a similar one as well and tested it out-- it worked fine so I was a little unsure what the issue with mine was.

I realized it was because one of my dates was text as there was an issue in that cell, I didn't realize it would cause an error for the entire formula. Sorry for my mistake, I'm not too well versed in excel.

Thanks so much for taking the time to assist me! I don't know how to close this thread as it's been completed.

Cheers,

 

[Fluff]

Glad you sorted it out & thanks for the feedback.