| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
To find rate of each item from item.xls and to copy price.xls
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
To find rate of each item from item.xls and to copy price.xls
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
To find rate of each item from item.xls and to copy price.xls
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
To find rate of each item from item.xls and to copy price.xls
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
To find rate of each item from item.xls and to copy price.xls
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 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]shee*t1!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 - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
To find rate of each item from item.xls and to copy price.xls
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 "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] shee*t1!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]shee*t1!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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
To find rate of each item from item.xls and to copy price.xls
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
To find rate of each item from item.xls and to copy price.xls
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) ?B?cG9s?= wrote in : 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 -- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| return the price of an item in a cell | Excel Worksheet Functions | |||
| Max Price and Min Price paid for an item - Rephrsed | Excel Worksheet Functions | |||
| Max Price and Min Price paid for an item | Excel Worksheet Functions | |||
| what template will figure out a %discount off an item price | Excel Discussion (Misc queries) | |||
| Most recent price per item | Excel Worksheet Functions | |||
Linear Mode
