Hi,

I have customer master codes in sheet 1, and customer codes and sales value
in sheet 2. I want to update sheet 1 with sales amounts from sheet 2, my
problem is that lookup is giving customers who do not appear in sheet 2 the
same sales value as the last customer code with sales value which makes me
manually delete amounts in these cells.
Illustration:
Sheet(1)
Customer Code Customer Name
12345 John
12346 James
12347 Joan
12348 Sam

Sheet (2)
Customer Code Sales Value ($)
12345 5,000
12346 1,500
12348 750

Sheet (1) after Lookup
Customer Code Customer Name Sales Value ($)
12345 John 5,000
12346 James 1,500
12347 Joan 1,500
12348 Sam 750
Sales value for 12347 is taken from 12346!

Is there any solution to this?

Thanks
--
tarig

Hi
I assume in sheet 2 your customer code is in column A starting in row 2 and
the amounts in Column B,then you want the results in sheet 1 in column C
starting row2
so in sheet1 C2 enter

=sumproduct(--(A1=sheet2!$A$1:$A$3000),sheet2!$B$1:$B$3000)

change the range to fit your needs, remember that both sides of the formula
has to be the same range if you ar using excel 2007 enter

=sumproduct(--(A1=sheet2!A:A),sheet2!B:B)

if this helps please click yes, thanks


"Tarig" wrote:

Hi,

I have customer master codes in sheet 1, and customer codes and sales value
in sheet 2. I want to update sheet 1 with sales amounts from sheet 2, my
problem is that lookup is giving customers who do not appear in sheet 2 the
same sales value as the last customer code with sales value which makes me
manually delete amounts in these cells.
Illustration:
Sheet(1)
Customer Code Customer Name
12345 John
12346 James
12347 Joan
12348 Sam

Sheet (2)
Customer Code Sales Value ($)
12345 5,000
12346 1,500
12348 750

Sheet (1) after Lookup
Customer Code Customer Name Sales Value ($)
12345 John 5,000
12346 James 1,500
12347 Joan 1,500
12348 Sam 750
Sales value for 12347 is taken from 12346!

Is there any solution to this?

Thanks
--
tarig