Array Formula Lookup based on two variables

[maxhewitt]

Good afternoon!

I'm trying to create an array formula that will return a text string from a list of text/Month items.

Here is what I have so far:

=IF(MONTH(Completed!D2:D4)=12,IF(MIN(Completed!B2:B4),Completed!A2:A4,"NONE"))

The idea is that Column D contains a list of file completion dates, Column B contains the original file dates and Column A contains the text value to return.

The formula should return the text from the row with the oldest original file date that was completed in December (or return "NONE" if nothing is found).

Neat idea, but it doesn't work. What am I doing wrong here?

 

[fairwinds]

Welcome to the board!

Try:

=IF(SUMPRODUCT(--(MONTH(C2:C20)=12)),LOOKUP(2,1/((B2:B20=MAX(IF(MONTH(C2:C20)=12,B2:B20)))*(MONTH(C2:C20)=12)),A2:A20),"NONE")

Normal enter.

 

[Aladin Akyurek]

Good afternoon!

I'm trying to create an array formula that will return a text string from a list of text/Month items.

Here is what I have so far:

=IF(MONTH(Completed!D2:D4)=12,IF(MIN(Completed!B2:B4),Completed!A2:A4,"NONE"))

The idea is that Column D contains a list of file completion dates, Column B contains the original file dates and Column A contains the text value to return.

The formula should return the text from the row with the oldest original file date that was completed in December (or return "NONE" if nothing is found).

Neat idea, but it doesn't work. What am I doing wrong here?

Let A2 on the formula sheet house a first day date of the month/year of interest like 1-Dec-04...

B2:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"NONE",INDEX(Completed!$A$2:$A$4,MATCH(MIN(IF(Completed!$D$2:$D$4-DAY(Completed!$D$2:$D$4)+1=A2,Completed!$B$2:$B$4,"")),IF(Completed!$D$2:$D$4-DAY(Completed!$D$2:$D$4)+1=A2,Completed!$B$2:$B$4,""),0))))

which must be confirmed with control+shift+enter.

If you have the morefunc.xll add-in:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"NONE",INDEX(Completed!$A$2:$A$4,MATCH(MIN(SETV(IF(Completed!$D$2:$D$4-DAY(Completed!$D$2:$D$4)+1=A2,Completed!$B$2:$B$4,""))),GETV(),0))))

which still must be confirmed with control+shift+enter.

You can also invoke smaller formulas in multiple cells to obtain the same result.