Prev: Convert FUMIFS to a format useable in Excel 2003
Next: Excel Cell Compares
From: pol on 12 Apr 2010 15:32
I have a column with name 'Item' which contain the code of the product.
Another sheet ( same file) contain code and profit.

Sheet1
item
A1
200
300
400
500
600

Sheet2
Item preofit
A1 B1
200 60
500 10
600 20
400 10

I have to find profit for the same item from sheet2 and to write in sheet1
column b1.

With many thanks
Pol
From: new1 on 12 Apr 2010 16:44
On 12 avr, 21:32, pol <p...(a)discussions.microsoft.com> wrote:
> I have a column with name  'Item' which contain the code of the product..
> Another sheet ( same file) contain code and profit.
>
> Sheet1
> item
> A1
> 200
> 300
> 400
> 500
> 600
>
> Sheet2
> Item          preofit
> A1              B1
> 200             60
> 500             10
> 600             20
> 400             10
>
> I have to find profit for the same item from sheet2  and to write in sheet1
> column b1.
>
> With many thanks
> Pol

Hello

If i understand well your question, this can be done with the help of
the vlookup function : this function is used to retrieve the profit of
a given item in sheet2, sheet which is used as a reference table.
In the B2 cell of Sheet1, try this formula

= VLOOKUP(A2;SHEET2!A:B;2;FALSE)

where
A2 is the cell containing the item code you're looking for;
SHEET2!A:B ( means the columns A and B of sheet 2) is used as the
profit per item reference table;
2 is the column rank from the reference table (defined above) from
which we want to retrieve the data
FALSE specifies that we're looking for the perfect match between
Sheet1!A1 and the values of Sheet2 colum A.

If it's ok, you can copy the formula on the other rows of Sheet1.

HTH

new1@[no/spam]realce.net



 | 
Pages: 1
Prev: Convert FUMIFS to a format useable in Excel 2003
Next: Excel Cell Compares