Looking up in a table


V

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 A1:D6.

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
 
Ad

Advertisements

T

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.
 
V

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
 
Ad

Advertisements

T

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
 

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