V
vsoler
I am observing some weird behaviour with VLOOKUP.
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
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