Understanding Array Formulas

[Rawrbear]

Hi,

I've recently been trying to further my excel knowledge, this has led me to try to understand array formulas better.

I was browsing the dashboard section and came across this thread:
http://chandoo.org/forum/threads/looking-for-solution-on-call-center-dash-board.16960/

(Note I've deliberately not opened any of the answer sheets as I wanted to try and build this myself)

I work with in a call centre so the data provided is very similar to things I encounter on a day to day basis.

I've tried to use:

{=SUM(IF(VLOOKUP('Base Data'!B2:B109,'Center Name'!A2:B109,2,FALSE)="Anjaybee",1,0)*'Base Data'!C2:C109)}

To calculate the Offered calls for one site however I'm getting an odd result which I don't understand and I'm hoping someone can explain. If I enter this formula into a single cell it returns 38766 which is the total of all sites offered calls however if I enter this formula across multiple cells it returns 24644 on all cells which is the answer I would expect.

My understanding of the array and how it would look at each step is as follows:

[skillset1, skillset2, skillset3.....]
do the vlookup
[Anjaybee, Kochar, Anjaybee...]
check if these are the site
[1, 0, 1...]
multiple it by the corrosponding offered calls
[1*148, 0*41, 1*137... ]
so
[148, 0, 137...]
Then sum these values
24644

I've attached the workbook with my outcomes, Can someone explain why the single cell array returns the total value and not a calulated value? I assume excel is only taking the first value for the vlookup and ignoring the rest but I don't understand why?

 

[Haseeb A]

VLOOKUP can only accept single lookup_value, but can accept multiple columns, ie {1,2,3...} will return each columns value in an array. If you have entered multiple lookup_value VLOOKUP will return only first one.

LOOKUP function will accept multiple lookup_values. But this needs lookup_range must be sorted in Ascending order (in your case 'Center Name'!A:A)

One way is to use MATCH function;

=SUM(IF(ISNUMBER(MATCH('Base Data'!$B$2:$B$109,IF('Center Name'!$B$2:$B$109=$A2,'Center Name'!$A$2:$A$109),0)),'Base Data'!C$2:C$109))

Edit:

You can also use SUMIF. In criteria part use an IF to check, 'take only skillset, if center = Anjaybee'

=SUM(SUMIF('Base Data'!$B:$B,IF('Center Name'!$B$2:$B$109=$A2,'Center Name'!$A$2:$A$109),'Base Data'!C:C))

Both are Array Entered