Trying to find the next closest date, only if the other column is "NO"

[Broncosfan007]

It's had to word exactly what I am trying to do, but in short I have billers in the I column, and each biller has a due date in column N, and I have whether or not the biller is paid ("YES", or "NO") in column H.

I simply want to use today's date and tell me what biller is due next, but only if the biller is not paid. So example...

If Today is 2/12/2016

H I N
NO Biller #1 2/22/2016
YES Biller #2 2/12/2016
YES Biller #3 2/17/2016
NO Biller #4 2/20/2016

So Biller #4 is the next biller due. I am just starting to learn more about Excel and I could really use help on this.

 

[ghrain22]

As an array(ctrl+shft+enter):

=Index($I$1:$I$10,Match(Min(If($H$1:$H$10 = "No", $N$1:$N10,"")),$N$1:$N$10,0))

the array is "confirmed" when the formula gets brackets {} wrapped around the formula

for legibility purposes

=Index(Biller,Match(Min(If(Status = "No", Date,"")),Date,0))

Unfortunately, this is a bit complex for a beginner, but I thought I'd try to explain. Working from inside out:

[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD]if(Status = "No", Date,"")[/TD]
[TD]returns the dates for status's that are "no" (otherwise returns nothing)[/TD]
[/TR]
[TR]
[TD]Min(...)[/TD]
[TD]returns the minimum date[/TD]
[/TR]
[TR]
[TD]Match(..., Date, 0)[/TD]
[TD]searches for the minimum date within the array of dates, and returns the row#(within the array).[/TD]
[/TR]
[TR]
[TD]Index(Billers, ...)[/TD]
[TD]returns the cell value of the specified row#(from match formula) within the specified array(the list of billers)[/TD]
[/TR]
</tbody>[/TABLE]


The concept of an array formula might be a bit confusing for a first timer. But it's necessary for the "if" function at the beginning. Easiest explanation for it is that you want the if statement to output multiple values (aka an "array") of values.

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

H​

[/td][td]

I​

[/td][td]

N​

[/td][td]

O​

[/td][td]

P​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td]

2/12/2016​

[/td][/tr]


[tr][td]

2​

[/td][td]NO[/td][td]Biller #1[/td][td]

2/22/2016​

[/td][td][/td][td]Next Due:[/td][/tr]


[tr][td]

3​

[/td][td]YES[/td][td]Biller #2[/td][td]

2/12/2016​

[/td][td][/td][td]Biller #4[/td][/tr]


[tr][td]

4​

[/td][td]YES[/td][td]Biller #3[/td][td]

2/17/2016​

[/td][td][/td][td]Biller #6[/td][/tr]


[tr][td]

5​

[/td][td]NO[/td][td]Biller #4[/td][td]

2/20/2016​

[/td][td][/td][td][/td][/tr]


[tr][td]

6​

[/td][td]NO[/td][td]Biller #5[/td][td]

2/21/2016​

[/td][td][/td][td][/td][/tr]


[tr][td]

7​

[/td][td]NO[/td][td]Biller #6[/td][td]

2/20/2016​

[/td][td][/td][td][/td][/tr]
[/table]

In P1 just enter:

=TODAY()<strike></strike>

In P3 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(I$2:I$7,SMALL(IF($N$2:$N$7=MIN(IF($H$2:$H$7="no",
    IF($N$2:$N$7>=$P$1,$N$2:$N$7))),ROW($I$2:$I$7)-ROW($I$2)+1),ROWS($P$3:P3))),"")

 

[Broncosfan007]

Thank you so much MRExcel MVP. This worked beautifully! I have been racking my brain so hard trying to figure this out, and I almost had it working, but because of the column with whether or not the biller was paid or not it kept messing me up lol.

 

[Aladin Akyurek]

Thank you so much MRExcel MVP. This worked beautifully! I have been racking my brain so hard trying to figure this out, and I almost had it working, but because of the column with whether or not the biller was paid or not it kept messing me up lol.

You are welcome.