Array Formula Lookup based on two variables

maxhewitt

New Member
Joined
Nov 29, 2005
Messages
1
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?
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
maxhewitt said:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,435
Messages
5,572,083
Members
412,440
Latest member
jaeremata
Top