Problem with VLOOKUP function

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
 
B

barry houdini

Hello Vicente,

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

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

confirmed with CTRL+SHIFT+ENTER
 
V

vsoler

Hello Vicente,

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

=SUM(ISNUMBER(MATCH(A1:A5;IF(D1:D3="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

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,C1:D3)="Fab"))

Note to the OP...

VLOOKUP can only lookup one lookup_value at a time.
 
B

barry houdini

T. Valko;486253 said:
*If* C1:C3 is sorted in ascending order as is shown in the sample dat
you
can use something like this:

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

There are some potential problems with that approach, though
Biff......

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

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.
 

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