LOOKUP function problem

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

I would like help please. I am writing a LOOKUP formula
that I want to use all the time, but the data may not
cooperate. Some times, for example, I want to look up a
code series such as 121, 122, 123, 124, and 125 in a
report. Then I will sum up the related numeric data from
another column.

But then the next month, only codes 121 and 124 may show
up on the report. When I run the SUM(LOOKUP)formula again,
Excel gives me an error because codes 122, 123, and 125
are not found in the search. Is there a way to leave in
the formula with all five codes, but have LOOKUP return
a "0" for any code that does not happen to show up that
month?

Any ideas would be appreciated. Thanks.
 
Hi
you may use SUMPRODUCT instead. e.g. your code is in column a and
column b contains your values. Try
=SUMPRODUCT(--(A1:A100={121,122,123,125,125}),B1:B100)
 
Hi Frank,

Just curious -- I guess I'm just a curious kinda guy. :-)

When I try your formula the way it is, I get a #Value error. If I
change the last comma to an asterisk, it works fine.

I'm wondering if it's just my version of Excel which has this problem.
I'm using a Macintosh version.

If it's not because of my Mac version, isn't it strange that it wouldn't
work with the comma syntax? I don't understand why that would be the
case.

Thanks!
 
You are correct Domenic, so one might as well remove the unary minuses

=SUMPRODUCT((A1:A100={121,122,123,124,125})*B1:B100)

will work

it's because

A1:A100={121,122,123,124,125} will create a 100 x 5 sized array
while the B1:V100 will be 100 x 1 and the built in (array1,array2) in
sumproduct can't manage that

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Thanks, Peo. I had a feeling there had to be a logical explanation.

With that in mind, I tried

=SUMPRODUCT(--(A1:A100={122,123}),--(B1:B100={2,3}))

and sure enough it worked since, as you know, both arrays are the same
size.

Thanks again!
 
Frank Kabel said:
you may use SUMPRODUCT instead. e.g. your code is in column a and
column b contains your values. Try
=SUMPRODUCT(--(A1:A100={121,122,123,125,125}),B1:B100)
Click to expand...
....

You should have used

=SUMPRODUCT(SUMIF(A1:A100,{121,122,123,124,125},B1:B100))

which is what's needed if B1:B100 could contain text.
 
Back
Top