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
"Roger Govier" wrote:
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
"Pete_UK" wrote in message
...
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
On Jul 15, 10:45 am, EricBB wrote:
try this,
=if(isna(VLOOKUP(A2,[item.xls]sheet1!A:C,3,0)),0,=VLOOKUP(A2,[item.xls]sheet1!A:C,3,0))
"pol" wrote:
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.
Pete
On Jul 15, 7:43 am, pol wrote:
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