If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Thanks for the help. I know I'll fully understand the formula structure
soon. One question, at end of formula ,4,0 what in fact does the 4 make reference to? "Ragdyer" wrote: Try this: =VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Thanks for the help. One further question. In the formul In sheet 2 $A$1:$b$10makes reference to two adjacent columns. If there is a column between so there is data in a and c and none in b the result return err,the formula in this cell referes to cells that are currently emply. How to get around this. ie" if sheet two has data in column a and column d. thanks George Yorks "Earl Kiosterud" wrote: George, In B1 of sheet 1: =VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE) Copy down with fill handle to B10. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Trying to create a formula to do the following: Sheet 1 column A a list of personal names a1-a10 Sheet 2 has list of names a1-a10 and list of dollar amounts colums d1-d10 want to search sheet one and if any name from sheet 2 found on sheet 1 than the corresponding dollar amount is entered. Any help appreciated. -- George |
#12
|
|||
|
|||
I've used the following formula =vlookup(b4,sheet3!$c$1:$m$31,11,0) The
correct data is transfered to the first cell in the column all other cells receive #N/A a value is not available to the formula or function. There is however data to be transfered.Any help appreciated "George A. Yorks" wrote: Thanks for the help. One further question. In the formul In sheet 2 $A$1:$b$10makes reference to two adjacent columns. If there is a column between so there is data in a and c and none in b the result return err,the formula in this cell referes to cells that are currently emply. How to get around this. ie" if sheet two has data in column a and column d. thanks George Yorks "Earl Kiosterud" wrote: George, In B1 of sheet 1: =VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE) Copy down with fill handle to B10. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Trying to create a formula to do the following: Sheet 1 column A a list of personal names a1-a10 Sheet 2 has list of names a1-a10 and list of dollar amounts colums d1-d10 want to search sheet one and if any name from sheet 2 found on sheet 1 than the corresponding dollar amount is entered. Any help appreciated. -- George |
#13
|
|||
|
|||
Well, Excel isn't seeing a match. For one of the formulas that you expect to
return a value, what is in column B, and what is the matching data in column C or your table on Sheet3? Could the problem be that you have numbers in one place and text that looks like a number (but is stored as text) in the other? If so, they won't match, e.g. 1 doesn't match "1" On Mon, 21 Feb 2005 13:55:04 -0800, George A. Yorks .(donotspam) wrote: I've used the following formula =vlookup(b4,sheet3!$c$1:$m$31,11,0) The correct data is transfered to the first cell in the column all other cells receive #N/A a value is not available to the formula or function. There is however data to be transfered.Any help appreciated "George A. Yorks" wrote: Thanks for the help. One further question. In the formul In sheet 2 $A$1:$b$10makes reference to two adjacent columns. If there is a column between so there is data in a and c and none in b the result return err,the formula in this cell referes to cells that are currently emply. How to get around this. ie" if sheet two has data in column a and column d. thanks George Yorks "Earl Kiosterud" wrote: George, In B1 of sheet 1: =VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE) Copy down with fill handle to B10. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Trying to create a formula to do the following: Sheet 1 column A a list of personal names a1-a10 Sheet 2 has list of names a1-a10 and list of dollar amounts colums d1-d10 want to search sheet one and if any name from sheet 2 found on sheet 1 than the corresponding dollar amount is entered. Any help appreciated. -- George |
#14
|
|||
|
|||
Ihave tried to copy data from USA Today report. When I past to excel
worksheet all cells are obliterated. Does this account for the data not being recognized. If so is there anything that can be done to make this data useable?? Thanks for all the help and information "Myrna Larson" wrote: Well, Excel isn't seeing a match. For one of the formulas that you expect to return a value, what is in column B, and what is the matching data in column C or your table on Sheet3? Could the problem be that you have numbers in one place and text that looks like a number (but is stored as text) in the other? If so, they won't match, e.g. 1 doesn't match "1" On Mon, 21 Feb 2005 13:55:04 -0800, George A. Yorks .(donotspam) wrote: I've used the following formula =vlookup(b4,sheet3!$c$1:$m$31,11,0) The correct data is transfered to the first cell in the column all other cells receive #N/A a value is not available to the formula or function. There is however data to be transfered.Any help appreciated "George A. Yorks" wrote: Thanks for the help. One further question. In the formul In sheet 2 $A$1:$b$10makes reference to two adjacent columns. If there is a column between so there is data in a and c and none in b the result return err,the formula in this cell referes to cells that are currently emply. How to get around this. ie" if sheet two has data in column a and column d. thanks George Yorks "Earl Kiosterud" wrote: George, In B1 of sheet 1: =VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE) Copy down with fill handle to B10. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Trying to create a formula to do the following: Sheet 1 column A a list of personal names a1-a10 Sheet 2 has list of names a1-a10 and list of dollar amounts colums d1-d10 want to search sheet one and if any name from sheet 2 found on sheet 1 than the corresponding dollar amount is entered. Any help appreciated. -- George |
#15
|
|||
|
|||
George,
The 4 is the third parameter of the VLOOKUP function, and tells it to retrieve the cell in the 4th column of the range being looked up in. Did I say being looked up in? Oh, well. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Thanks for the help. I know I'll fully understand the formula structure soon. One question, at end of formula ,4,0 what in fact does the 4 make reference to? "Ragdyer" wrote: Try this: =VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Thanks for the help. One further question. In the formul In sheet 2 $A$1:$b$10makes reference to two adjacent columns. If there is a column between so there is data in a and c and none in b the result return err,the formula in this cell referes to cells that are currently emply. How to get around this. ie" if sheet two has data in column a and column d. thanks George Yorks "Earl Kiosterud" wrote: George, In B1 of sheet 1: =VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE) Copy down with fill handle to B10. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Trying to create a formula to do the following: Sheet 1 column A a list of personal names a1-a10 Sheet 2 has list of names a1-a10 and list of dollar amounts colums d1-d10 want to search sheet one and if any name from sheet 2 found on sheet 1 than the corresponding dollar amount is entered. Any help appreciated. -- George |
#16
|
|||
|
|||
I keep saying thanks, your help is outstanding. I would like to ask two
additional questions. ie: When I enter my formula into the cells of column A in a number of cells it returns a value in a few it returns#N/A. There is no data in those cases but with the #n?A when I try to add the columns will not do so as it cant enter a non digit. I'm using 0 for the last number in my formula. How best to get around this. Also I've copied a table from USA internet. In pasting to worksheet all the cells are obliterated and nothing is recognized by excel. The data is however recognized in (pardon me) lotus. Is there anyway of having my vlookup formula search 123 in place of a sheet in excel. =vlookup(a1, sheet2!$A$1:$j$31, 10,0). Hope these questions make sense to someone "Earl Kiosterud" wrote: George, The 4 is the third parameter of the VLOOKUP function, and tells it to retrieve the cell in the 4th column of the range being looked up in. Did I say being looked up in? Oh, well. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Thanks for the help. I know I'll fully understand the formula structure soon. One question, at end of formula ,4,0 what in fact does the 4 make reference to? "Ragdyer" wrote: Try this: =VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Thanks for the help. One further question. In the formul In sheet 2 $A$1:$b$10makes reference to two adjacent columns. If there is a column between so there is data in a and c and none in b the result return err,the formula in this cell referes to cells that are currently emply. How to get around this. ie" if sheet two has data in column a and column d. thanks George Yorks "Earl Kiosterud" wrote: George, In B1 of sheet 1: =VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE) Copy down with fill handle to B10. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Trying to create a formula to do the following: Sheet 1 column A a list of personal names a1-a10 Sheet 2 has list of names a1-a10 and list of dollar amounts colums d1-d10 want to search sheet one and if any name from sheet 2 found on sheet 1 than the corresponding dollar amount is entered. Any help appreciated. -- George |
#17
|
|||
|
|||
First question:
Replace error message with a null ( "" ), which can then be added: =IF(ISNA(MATCH(A1,'Sheet2'!$A$1:$A$10,0)),"",VLOOK UP(A1, 'Sheet2'!$A$1:$D$10, 4, 0) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "George A. Yorks" .(donotspam) wrote in message news I keep saying thanks, your help is outstanding. I would like to ask two additional questions. ie: When I enter my formula into the cells of column A in a number of cells it returns a value in a few it returns#N/A. There is no data in those cases but with the #n?A when I try to add the columns will not do so as it cant enter a non digit. I'm using 0 for the last number in my formula. How best to get around this. Also I've copied a table from USA internet. In pasting to worksheet all the cells are obliterated and nothing is recognized by excel. The data is however recognized in (pardon me) lotus. Is there anyway of having my vlookup formula search 123 in place of a sheet in excel. =vlookup(a1, sheet2!$A$1:$j$31, 10,0). Hope these questions make sense to someone "Earl Kiosterud" wrote: George, The 4 is the third parameter of the VLOOKUP function, and tells it to retrieve the cell in the 4th column of the range being looked up in. Did I say being looked up in? Oh, well. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Thanks for the help. I know I'll fully understand the formula structure soon. One question, at end of formula ,4,0 what in fact does the 4 make reference to? "Ragdyer" wrote: Try this: =VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0) -- HTH, RD ------------------------------------------------------------------------- -- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------- -- "George A. Yorks" .(donotspam) wrote in message ... Thanks for the help. One further question. In the formul In sheet 2 $A$1:$b$10makes reference to two adjacent columns. If there is a column between so there is data in a and c and none in b the result return err,the formula in this cell referes to cells that are currently emply. How to get around this. ie" if sheet two has data in column a and column d. thanks George Yorks "Earl Kiosterud" wrote: George, In B1 of sheet 1: =VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE) Copy down with fill handle to B10. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Trying to create a formula to do the following: Sheet 1 column A a list of personal names a1-a10 Sheet 2 has list of names a1-a10 and list of dollar amounts colums d1-d10 want to search sheet one and if any name from sheet 2 found on sheet 1 than the corresponding dollar amount is entered. Any help appreciated. -- George |
#18
|
|||
|
|||
I tried using the formula belowfrom =IF to 4,0) and got message too many
arguments. Bottom line it does not change the #N/A to ) which will allow the column to be added. Any other thoughts. Thanks much "RagDyer" wrote: First question: Replace error message with a null ( "" ), which can then be added: =IF(ISNA(MATCH(A1,'Sheet2'!$A$1:$A$10,0)),"",VLOOK UP(A1, 'Sheet2'!$A$1:$D$10, 4, 0) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "George A. Yorks" .(donotspam) wrote in message news I keep saying thanks, your help is outstanding. I would like to ask two additional questions. ie: When I enter my formula into the cells of column A in a number of cells it returns a value in a few it returns#N/A. There is no data in those cases but with the #n?A when I try to add the columns will not do so as it cant enter a non digit. I'm using 0 for the last number in my formula. How best to get around this. Also I've copied a table from USA internet. In pasting to worksheet all the cells are obliterated and nothing is recognized by excel. The data is however recognized in (pardon me) lotus. Is there anyway of having my vlookup formula search 123 in place of a sheet in excel. =vlookup(a1, sheet2!$A$1:$j$31, 10,0). Hope these questions make sense to someone "Earl Kiosterud" wrote: George, The 4 is the third parameter of the VLOOKUP function, and tells it to retrieve the cell in the 4th column of the range being looked up in. Did I say being looked up in? Oh, well. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Thanks for the help. I know I'll fully understand the formula structure soon. One question, at end of formula ,4,0 what in fact does the 4 make reference to? "Ragdyer" wrote: Try this: =VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0) -- HTH, RD ------------------------------------------------------------------------- -- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------- -- "George A. Yorks" .(donotspam) wrote in message ... Thanks for the help. One further question. In the formul In sheet 2 $A$1:$b$10makes reference to two adjacent columns. If there is a column between so there is data in a and c and none in b the result return err,the formula in this cell referes to cells that are currently emply. How to get around this. ie" if sheet two has data in column a and column d. thanks George Yorks "Earl Kiosterud" wrote: George, In B1 of sheet 1: =VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE) Copy down with fill handle to B10. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Trying to create a formula to do the following: Sheet 1 column A a list of personal names a1-a10 Sheet 2 has list of names a1-a10 and list of dollar amounts colums d1-d10 want to search sheet one and if any name from sheet 2 found on sheet 1 than the corresponding dollar amount is entered. Any help appreciated. -- George |
#19
|
|||
|
|||
I can see that I left out a parenthesis.
I tested this against your scenario in your original post, and this *does* work: =IF(ISNA(MATCH(A1,Sheet2!$A$1:$A$10,0)),"",VLOOKUP (A1,Sheet2!$A$1:$D$10,4,0) ) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... I tried using the formula belowfrom =IF to 4,0) and got message too many arguments. Bottom line it does not change the #N/A to ) which will allow the column to be added. Any other thoughts. Thanks much "RagDyer" wrote: First question: Replace error message with a null ( "" ), which can then be added: =IF(ISNA(MATCH(A1,'Sheet2'!$A$1:$A$10,0)),"",VLOOK UP(A1, 'Sheet2'!$A$1:$D$10, 4, 0) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "George A. Yorks" .(donotspam) wrote in message news I keep saying thanks, your help is outstanding. I would like to ask two additional questions. ie: When I enter my formula into the cells of column A in a number of cells it returns a value in a few it returns#N/A. There is no data in those cases but with the #n?A when I try to add the columns will not do so as it cant enter a non digit. I'm using 0 for the last number in my formula. How best to get around this. Also I've copied a table from USA internet. In pasting to worksheet all the cells are obliterated and nothing is recognized by excel. The data is however recognized in (pardon me) lotus. Is there anyway of having my vlookup formula search 123 in place of a sheet in excel. =vlookup(a1, sheet2!$A$1:$j$31, 10,0). Hope these questions make sense to someone "Earl Kiosterud" wrote: George, The 4 is the third parameter of the VLOOKUP function, and tells it to retrieve the cell in the 4th column of the range being looked up in. Did I say being looked up in? Oh, well. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Thanks for the help. I know I'll fully understand the formula structure soon. One question, at end of formula ,4,0 what in fact does the 4 make reference to? "Ragdyer" wrote: Try this: =VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0) -- HTH, RD ------------------------------------------------------------------------- -- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------- -- "George A. Yorks" .(donotspam) wrote in message ... Thanks for the help. One further question. In the formul In sheet 2 $A$1:$b$10makes reference to two adjacent columns. If there is a column between so there is data in a and c and none in b the result return err,the formula in this cell referes to cells that are currently emply. How to get around this. ie" if sheet two has data in column a and column d. thanks George Yorks "Earl Kiosterud" wrote: George, In B1 of sheet 1: =VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE) Copy down with fill handle to B10. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Trying to create a formula to do the following: Sheet 1 column A a list of personal names a1-a10 Sheet 2 has list of names a1-a10 and list of dollar amounts colums d1-d10 want to search sheet one and if any name from sheet 2 found on sheet 1 than the corresponding dollar amount is entered. Any help appreciated. -- George |
#20
|
|||
|
|||
Thank you,
Your corrected formula works well with one lasting problem. If I remove the data that was used for the search all the results of the search are wiped out. I tried to remove the data to use new data for new search. Can anything be done to correct this situation??? "Ragdyer" wrote: I can see that I left out a parenthesis. I tested this against your scenario in your original post, and this *does* work: =IF(ISNA(MATCH(A1,Sheet2!$A$1:$A$10,0)),"",VLOOKUP (A1,Sheet2!$A$1:$D$10,4,0) ) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... I tried using the formula belowfrom =IF to 4,0) and got message too many arguments. Bottom line it does not change the #N/A to ) which will allow the column to be added. Any other thoughts. Thanks much "RagDyer" wrote: First question: Replace error message with a null ( "" ), which can then be added: =IF(ISNA(MATCH(A1,'Sheet2'!$A$1:$A$10,0)),"",VLOOK UP(A1, 'Sheet2'!$A$1:$D$10, 4, 0) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "George A. Yorks" .(donotspam) wrote in message news I keep saying thanks, your help is outstanding. I would like to ask two additional questions. ie: When I enter my formula into the cells of column A in a number of cells it returns a value in a few it returns#N/A. There is no data in those cases but with the #n?A when I try to add the columns will not do so as it cant enter a non digit. I'm using 0 for the last number in my formula. How best to get around this. Also I've copied a table from USA internet. In pasting to worksheet all the cells are obliterated and nothing is recognized by excel. The data is however recognized in (pardon me) lotus. Is there anyway of having my vlookup formula search 123 in place of a sheet in excel. =vlookup(a1, sheet2!$A$1:$j$31, 10,0). Hope these questions make sense to someone "Earl Kiosterud" wrote: George, The 4 is the third parameter of the VLOOKUP function, and tells it to retrieve the cell in the 4th column of the range being looked up in. Did I say being looked up in? Oh, well. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Thanks for the help. I know I'll fully understand the formula structure soon. One question, at end of formula ,4,0 what in fact does the 4 make reference to? "Ragdyer" wrote: Try this: =VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0) -- HTH, RD ------------------------------------------------------------------------- -- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------- -- "George A. Yorks" .(donotspam) wrote in message ... Thanks for the help. One further question. In the formul In sheet 2 $A$1:$b$10makes reference to two adjacent columns. If there is a column between so there is data in a and c and none in b the result return err,the formula in this cell referes to cells that are currently emply. How to get around this. ie" if sheet two has data in column a and column d. thanks George Yorks "Earl Kiosterud" wrote: George, In B1 of sheet 1: =VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE) Copy down with fill handle to B10. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Trying to create a formula to do the following: Sheet 1 column A a list of personal names a1-a10 Sheet 2 has list of names a1-a10 and list of dollar amounts colums d1-d10 want to search sheet one and if any name from sheet 2 found on sheet 1 than the corresponding dollar amount is entered. Any help appreciated. -- George |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Using Mail Merge for Creating a Document of Biographies... | Sam Clarke | General Discussion | 1 | January 4th, 2005 04:28 PM |
Using Mail Merge for Creating a Document of Biographies... | Sam Clarke | Mailmerge | 1 | December 17th, 2004 10:17 AM |
Using Mail Merge for Creating a Document of Biographies... | Sam Clarke | General Discussions | 0 | December 16th, 2004 07:35 PM |
Creating Forms using VBA | Suzi | General Discussion | 1 | November 18th, 2004 03:46 PM |
Problem Creating Report | [email protected] | Using Forms | 1 | June 7th, 2004 07:51 AM |