To find rate of each item from item.xls and to copy price.xls

[pol]

I have two worksheet. How I can locate the corresponding price in item.xls
and to write in price.xls. Please help

1. Item.xls
2. price.xls

Item.xls
A B C
Code, description, rate
001 TV 12
002 COMPUTER 5
004 RECORD 6

Price.xls
A B
code rate
001 12
003 0
004 6

 

[Pete_UK]

You can use VLOOKUP to do this. Assuming both files are open at the
same time, something like this in B2 of price.xls:

=VLOOKUP(A2,[item.xls]sheet1!A:C,3,0)

I assume you have two separate workbooks - it's confusing to refer to
them as worksheets and then give them filenames.

Hope this helps.

Pete

 

[pol]

Thanks for the reply and it is working fine. But if there is no match the
result will show as '#N/A' if there is no match in item.xls , the result
should be 0. How I can bring it as 0 instead of '#NA'.

Thanks a lote

You can use VLOOKUP to do this. Assuming both files are open at the
same time, something like this in B2 of price.xls:

=VLOOKUP(A2,[item.xls]sheet1!A:C,3,0)

I assume you have two separate workbooks - it's confusing to refer to
them as worksheets and then give them filenames.

Hope this helps.

Pete

I have two worksheet. How I can locate the corresponding price in item.xls
and to write in price.xls. Please help

1. Item.xls
2. price.xls

Item.xls
A B C
Code, description, rate
001 TV 12
002 COMPUTER 5
004 RECORD 6

Price.xls
A B
code rate
001 12
003 0
004 6

 

[EricBB]

try this,
=if(isna(VLOOKUP(A2,[item.xls]sheet1!A:C,3,0)),0,=VLOOKUP(A2,[item.xls]sheet1!A:C,3,0))

Thanks for the reply and it is working fine. But if there is no match the
result will show as '#N/A' if there is no match in item.xls , the result
should be 0. How I can bring it as 0 instead of '#NA'.

Thanks a lote

You can use VLOOKUP to do this. Assuming both files are open at the
same time, something like this in B2 of price.xls:

=VLOOKUP(A2,[item.xls]sheet1!A:C,3,0)

I assume you have two separate workbooks - it's confusing to refer to
them as worksheets and then give them filenames.

Hope this helps.

Pete

I have two worksheet. How I can locate the corresponding price in item.xls
and to write in price.xls. Please help

1. Item.xls
2. price.xls

Item.xls
A B C
Code, description, rate
001 TV 12
002 COMPUTER 5
004 RECORD 6

Price.xls
A B
code rate
001 12
003 0
004 6

 

[Pete_UK]

A minor correction - you don't need the second = sign:

=if(isna(VLOOKUP(A2,[item.xls]sheet1!A:C,3,0)),0,VLOOKUP(A2,[item.xls]
shee­t1!A:C,3,0))

Hope this helps.

Pete

try this,
=if(isna(VLOOKUP(A2,[item.xls]sheet1!A:C,3,0)),0,=VLOOKUP(A2,[item.xls]shee­t1!A:C,3,0))

Thanks for the reply and it is working fine. But if there is no match the
result will show as '#N/A'  if there is no match in item.xls , the result
should be 0. How I can bring it as 0 instead of '#NA'.

Thanks a lote

You can use VLOOKUP to do this. Assuming both files are open at the
same time, something like this in B2 of price.xls:
=VLOOKUP(A2,[item.xls]sheet1!A:C,3,0)
I assume you have two separate workbooks - it's confusing to refer to
them as worksheets and then give them filenames.
Hope this helps.
Pete
I have two worksheet. How I can locate the corresponding price in item.xls  
and to write in price.xls. Please help
1. Item.xls
2. price.xls
Item.xls
A       B                    C
Code, description,    rate
001    TV                 12
002    COMPUTER     5
004   RECORD          6
Price.xls
A          B
code      rate
001       12
003       0
004       6- Hide quoted text -

- Show quoted text -

 

[Roger Govier]

Hi

And slightly more efficient would be
=IF(COUNTIF([item.xls]Sheet1!A:A,A2),
VLOOKUP(A2,[item.xls]Shee­t1!A:C,3,0),"")


--
Regards
Roger Govier

A minor correction - you don't need the second = sign:

=if(isna(VLOOKUP(A2,[item.xls]sheet1!A:C,3,0)),0,VLOOKUP(A2,[item.xls]
shee­t1!A:C,3,0))

Hope this helps.

Pete

try this,
=if(isna(VLOOKUP(A2,[item.xls]sheet1!A:C,3,0)),0,=VLOOKUP(A2,[item.xls]shee­t1!A:C,3,0))

Thanks for the reply and it is working fine. But if there is no match
the
result will show as '#N/A' if there is no match in item.xls , the
result
should be 0. How I can bring it as 0 instead of '#NA'.

Thanks a lote

"Pete_UK" wrote:

You can use VLOOKUP to do this. Assuming both files are open at the
same time, something like this in B2 of price.xls:

=VLOOKUP(A2,[item.xls]sheet1!A:C,3,0)

I assume you have two separate workbooks - it's confusing to refer to
them as worksheets and then give them filenames.

Hope this helps.

I have two worksheet. How I can locate the corresponding price in
item.xls
and to write in price.xls. Please help

1. Item.xls
2. price.xls

Item.xls
A B C
Code, description, rate
001 TV 12
002 COMPUTER 5
004 RECORD 6

Price.xls
A B
code rate
001 12
003 0
004 6- Hide quoted text -

- Show quoted text -

__________ Information from ESET Smart Security, version of virus
signature database 4245 (20090715) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4245 (20090715) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[pol]

Thanks for the reply. But I have one more doubt
I given the following formula

IF(COUNTIF([item.xls]sheet1!A:C,G7),TRUE,FALSE) , the result is showing as
true if the record exist in item.xls . At the same time I wrote another
VLOOKUP(G7,[item.xls]sheet1!A:C,3,0) the result is showing as '#NA' for the
same record which already showed as true in Countif .

What may be the reason for showing diffrent result in both function

Hi

And slightly more efficient would be
=IF(COUNTIF([item.xls]Sheet1!A:A,A2),
VLOOKUP(A2,[item.xls]Sheet1!A:C,3,0),"")


--
Regards
Roger Govier

A minor correction - you don't need the second = sign:

=if(isna(VLOOKUP(A2,[item.xls]sheet1!A:C,3,0)),0,VLOOKUP(A2,[item.xls]
sheet1!A:C,3,0))

Hope this helps.

Pete

try this,
=if(isna(VLOOKUP(A2,[item.xls]sheet1!A:C,3,0)),0,=VLOOKUP(A2,[item.xls]sheet1!A:C,3,0))



:
Thanks for the reply and it is working fine. But if there is no match
the
result will show as '#N/A' if there is no match in item.xls , the
result
should be 0. How I can bring it as 0 instead of '#NA'.

Thanks a lote

:

You can use VLOOKUP to do this. Assuming both files are open at the
same time, something like this in B2 of price.xls:

=VLOOKUP(A2,[item.xls]sheet1!A:C,3,0)

I assume you have two separate workbooks - it's confusing to refer to
them as worksheets and then give them filenames.

Hope this helps.

Pete

I have two worksheet. How I can locate the corresponding price in
item.xls
and to write in price.xls. Please help

1. Item.xls
2. price.xls

Item.xls
A B C
Code, description, rate
001 TV 12
002 COMPUTER 5
004 RECORD 6

Price.xls
A B
code rate
001 12
003 0
004 6- Hide quoted text -

- Show quoted text -

__________ Information from ESET Smart Security, version of virus
signature database 4245 (20090715) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4245 (20090715) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Liliana]

For an exact match
=VLOOKUP(A1,[Item.xls]SheetName!A2:C4,3,FALSE)

The above returns #N/A if no match


To find the largest value < lookup value
=VLOOKUP(A1,[Item.xls]SheetName!A2:C4,3)


Substitute SheetName with the name of the worksheet containing the
lookup table.

Better to use a named range

=VLOOKUP(A1,[Item.xls]SheetName!yourNamedRange,3)

I have two worksheet. How I can locate the corresponding price in
item.xls and to write in price.xls. Please help

1. Item.xls
2. price.xls

Item.xls
A B C
Code, description, rate
001 TV 12
002 COMPUTER 5
004 RECORD 6

Price.xls
A B
code rate
001 12
003 0
004 6

--