Array Formula Using LOOKUP function.

L

luchshel

I hope someone can help me on this.

I am using a third party software on top of an Excel Workbook. This
software does not support the use of the Excel function INDIRECT.

The following formula works in my Excel model return the expected value of
201.

={SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF(DepartmentID=$C8,INDIRECT(LOOKUP(I3,COA!N3:N14,COA!Q3:Q14)),0),0),0))}

Unfortunately, when I remove the INDIRECT function from the formula a value
of ZERO (0) is returned.

So,

={SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF(DepartmentID=$C8,LOOKUP(I3,COA!N3:N14,COA!Q3:Q14),0),0),0))}

Doesn't work.

I have tried everything I can think of. The problem I have is the data I
wish to SUM in the SUM(IF formula is a variable column reference based upon
separate criteria.

Any ideas would be appreciated.
 
L

luchshel

Barb,

A list of twelve text entries equal to range names I have defined in the
workbook.

AOCT
ANOV
ADEC
AJAN
AFEB
AMAR
AAPR
AMAY
AJUN
AJUL
AAUG
ASEP
 
R

Ron Coderre

Maybe something like this array formula?:

=SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF(DepartmentID=$C8,CHOOSE(MATCH(I3,COA!N3:N14,0),AOCT,ANOV,ADEC,AJAN,AFEB,AMAR,AAPR,AMAY,AJUN,AJUL,AAUG,ASEP)),0),0))

Note: Since text wrap may impact the display, there are no space in that
formula.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
R

Ron Coderre

Actually, this regular formula may be easiser:

=IF(AND(AccountID=$J$1,BusinessUnitID=$J$2,DepartmentID=$C8),SUM(CHOOSE(MATCH(I3,COA!N3:N14,0),AOCT,ANOV,ADEC,AJAN,AFEB,AMAR,AAPR,AMAY,AJUN,AJUL,AAUG,ASEP)),0)


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
T

T. Valko

I guess your assumption is that the defined names: AccountID,
BusinessUnitID and DepartmentID are *single* cells?

Bet'cha an adult beverage they're arrays!

Sumproduct

Biff
 
R

Ron Coderre

I was pretty much concentrating on the right side of the formula, but as Biff
suspected and luchshel confirmed, all of the named ranges are multi-cell
arrays.

Consequently.....How about this regular formula?:

=SUMPRODUCT((AccountID=$J$1)*(BusinessUnitID=$J$2)*(DepartmentID=$C8)*(CHOOSE(MATCH(I3,COA!N3:N14,0),AOCT,ANOV,ADEC,AJAN,AFEB,AMAR,AAPR,AMAY,AJUN,AJUL,AAUG,ASEP)))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
L

luchshel

A debt is owed to you my friend.

Everyting appears to be in working order.

Many thanks for your assistance Ron.

I'll cover Ron on the adult beverage.

Thank you.

C
 
R

Ron Coderre

Thanks for the feedback; I'm so glad you found that helpful.......

BTW...my beverage of choice is "coffee milk". (It's a regional thing)

***********
Regards,
Ron

XL2002, WinXP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top