Problem with VLOOKUP function

[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

 

[vsoler]

Hello Vicente,

I assume tou mean C13 rather than C1:E3.....in which case you could
use this formula

=SUM(ISNUMBER(MATCH(A1:A5;IF(D13="Fab";C1:C3;"");0))+0)

confirmed with CTRL+SHIFT+ENTER

Thank you.

I can see that many formulas use the combination ISNUMBER/MATCH. I'll
remember it!

 

[T. Valko]

*If* C1:C3 is sorted in ascending order as is shown in the sample data you
can use something like this:

=SUMPRODUCT(--(LOOKUP(A1:A5,C13)="Fab"))

Note to the OP...

VLOOKUP can only lookup one lookup_value at a time.

 

[T. Valko]

Any blanks in A1:A5 and the formula returns #N/A.....

and 5 entries of, say CCz in A1:A5 will produce a result of 5.....

My suggestion is based on the sample data posted.