vlook up

Hi All,
I have one table with Item#, order# and Sales. In another spreadsheet I am
creating table for each item seperately to see the sales. When I use vlookup,
it doesnt work because source has same item multiple times. I am changing the
range by one raw down once I do vlookup to avoid the first data taken but
then if lookup value is repeating at the end then it will pull so many times.

If you dont understand the question. Please try to use vlookup from a table
where there is one item more than once. and use vlook up for more than once.

Thanks,
Sean

Hi Sean,

It is clear how VLOOKUP behaves when there are more than one identical items
in the first column. What's unclear is what you are actually trying to
achieve.

If what you want is get a sum of all sales amounts that correspond to a
repeted item then you could do something like this:

=SUMIF(A1:A10,"myItem",B1:B10)

or

=SUMPRODUCT((A1:A10="myItem")*B1:B10)

Regards,
KL


"Sean" <Sean@discussions.microsoft.com> wrote in message
news:555B3707-E321-43B3-BCBF-F87189173225@microsoft.com...
> Hi All,
> I have one table with Item#, order# and Sales. In another spreadsheet I am
> creating table for each item seperately to see the sales. When I use
> vlookup,
> it doesnt work because source has same item multiple times. I am changing
> the
> range by one raw down once I do vlookup to avoid the first data taken but
> then if lookup value is repeating at the end then it will pull so many
> times.
>
> If you dont understand the question. Please try to use vlookup from a
> table
> where there is one item more than once. and use vlook up for more than
> once.
>
> Thanks,
> Sean

Hi Sean,

It is clear how VLOOKUP behaves when there are more than one identical items
in the first column. What's unclear is what you are actually trying to
achieve.

If what you want is get a sum of all sales amounts that correspond to a
repeted item then you could do something like this:

=SUMIF(A1:A10,"myItem",B1:B10)

or

=SUMPRODUCT((A1:A10="myItem")*B1:B10)

Regards,
KL


"Sean" <Sean@discussions.microsoft.com> wrote in message
news:555B3707-E321-43B3-BCBF-F87189173225@microsoft.com...
> Hi All,
> I have one table with Item#, order# and Sales. In another spreadsheet I am
> creating table for each item seperately to see the sales. When I use
> vlookup,
> it doesnt work because source has same item multiple times. I am changing
> the
> range by one raw down once I do vlookup to avoid the first data taken but
> then if lookup value is repeating at the end then it will pull so many
> times.
>
> If you dont understand the question. Please try to use vlookup from a
> table
> where there is one item more than once. and use vlook up for more than
> once.
>
> Thanks,
> Sean