Looking up in a table

[vsoler]

Hello,

My problem is the following:

I have a long table of expenses by cost center (CC). Its structure is
(once simplified) like this:

CC Cta Bgt Act
CCa Cta1 1 3
CCb Cta2 2 4
CCc Cta3 3 5
CCa Cta2 4 1
CCc Cta1 5 7

Totals 15 20

(CC= Cost Center, Bgt= Budget, Act=Actual)
Say it is in A16.

On the other side I have a lookup table that defines how I want to
group my CC for analysis:

CC CCNom CCGr1
CCa Centro A Fab
CCb Centro B Adm
CCc Centro C Fab

(CCa and CCc will be grouped into "Fab", CCb will be grouped into
"Adm")
Say it is in A20:C23

Given a certain value in cell A30 (say for example "Fab") I need to
calculate the total value for its costs centers (CCa & CCc). In the
example given it will be 1+3+4+5 = 13

I started with a simple index/match formula, --(INDEX(C21:C23,MATCH
(A2:A6,A21:23),0))=$A$30) that works, since I can see that the result,
using Ctrl-Shft-Enter is {1\0\1\1\1}

The problem comes when I want to multiply this vector by the Bgt.

I would have expected that SUMPRODUCT(C2:C6;--(INDEX(C21:C23,MATCH
(A2:A6,A21:23),0))=$A$30)) would give me the correct result, but it
does not.

What am I doing wrong?

Best regards

 

[T. Valko]

SUMPRODUCT(C2:C6;--(INDEX(C21:C23,MATCH(A2:A6,A21:23),0))=$A$30))

Aside from the syntax errors, INDEX is only returning a *single* element.

Try this array formula** :

=SUM(IF(ISNUMBER(MATCH(A2:A6,IF(ISNUMBER(MATCH(C21:C23,A30,0)),A21:A23),0)),C2:C6))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[vsoler]

Aside from the syntax errors, INDEX is only returning a *single* element.

Try this array formula** :

=SUM(IF(ISNUMBER(MATCH(A2:A6,IF(ISNUMBER(MATCH(C21:C23,A30,0)),A21:A23),0)),C2:C6))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

T Valko,

Your a genious!!!

Your answer suits my needs very well, since it does not require the
lookup table to be sorted.

I've found an alternative solution using SUMPRODUCT and LOOKUP but
requires the lookup table to be sorted.

Do you think that an alternative formula with SUMPRODUCT, SUM or SUMIF
is possible for an unsorted lookup table?

Thank you for your interest.

Vicente Soler

 

[T. Valko]

Do you think that an alternative formula with

SUMPRODUCT, SUM or SUMIF is possible
for an unsorted lookup table?

Do you mean alternatives for this formula:

=SUM(IF(ISNUMBER(MATCH(A2:A6,IF(ISNUMBER(MATCH(C21:C23,A30,0)),A21:A23),0)),C2:C6))

Or, are you talking about a different application altogether?

Here's an alternative to the above formula, it's a few keystrokes shorter.

Array entered** :

=SUM(SUMIF(A2:A6,IF(ISNUMBER(MATCH(H2:H4,A15,0)),F2:F4),C2:C6))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Both of those formulas have identical calculation times.

--
Biff
Microsoft Excel MVP

Aside from the syntax errors, INDEX is only returning a *single* element.

Try this array formula** :

=SUM(IF(ISNUMBER(MATCH(A2:A6,IF(ISNUMBER(MATCH(C21:C23,A30,0)),A21:A23),0)),C2:C6))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

T Valko,

Your a genious!!!

Your answer suits my needs very well, since it does not require the
lookup table to be sorted.

I've found an alternative solution using SUMPRODUCT and LOOKUP but
requires the lookup table to be sorted.

Do you think that an alternative formula with SUMPRODUCT, SUM or SUMIF
is possible for an unsorted lookup table?

Thank you for your interest.

Vicente Soler