V

#### vsoler

Let me explain what happens:

In A1:A5 I have some codes

CCa

CCb

CCc

CCa

CCc

In C1:E3 I have a range that shows how they should be grouped

together:

CCa Fab

CCb Adm

CCc Fab

(that is, CCa and CCc belong to the Fab group while CCb belongs to the

Adm group)

I want to count the number of codes that fall under the, say, Fab

group.

My array formula in F1 is:

={SUM(IF(VLOOKUP(A1:A5,C1:E3;2)="Fab";1;0))}

but it gives me the answer 1, which is obviously wrong.

However, if I input the same formula in H1:H5 (one single array

formula in the 5 cells) the I get 4 in each of the cells, which is

correct.

I have followed how the formulas are evaluated by means of the

"evaluate Formula" icon and I simply think that VLOOKUP is

misbehaving. Or alternatively, we could say that it has been poorly

designed.

Am I doing anything wrong? What formula should I use in F1?

Thank you

Vicente Soler