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
I bet you want to use =vlookup().
Debra Dalgleish has some nice instructions at:
http://www.contextures.com/xlFunctions02.html
George A. Yorks wrote:
>
> 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
--
Dave Peterson
You will need to use the vlookup formula. Have a look at
this in the help menu. It's quite simple, and compares to
lists to return a value.
=vlookup(a1:a10,B1:c10,2,false)
In this case, it looks up the values in cells a1 to a10
and compares them to the values in cells b1 to b10 and
returns the second value to thr right of these cells;
that is, the values in column c.
Hope that helps.
>-----Original 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
>.
>
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" <[email protected].(donotspam)> wrote in message
news:[email protected]...
> 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
Thank you for all the help. I am experiencing one little problem. I write
the formula into cell E1 and attempt to copy down to the last cell using the
fill handle. I seems as though every second cell comes up with #N/A. Any
ideas what is causing this.
Again thank you for all the help
"James" wrote:
> You will need to use the vlookup formula. Have a look at
> this in the help menu. It's quite simple, and compares to
> lists to return a value.
>
> =vlookup(a1:a10,B1:c10,2,false)
>
> In this case, it looks up the values in cells a1 to a10
> and compares them to the values in cells b1 to b10 and
> returns the second value to thr right of these cells;
> that is, the values in column c.
>
> Hope that helps.
>
> >-----Original 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
> >.
> >
>
This is an array formula (since the 1st argument to VLOOKUP is not a single
cell, but 10 cells). I would also change the references to absolute, i.e.
=VLOOKUP($A$1:$A$10,$B$1:$C$10,2,0)
Then, to use this, select the 10 cells, E1:E10, and enter the above formula in
E1, and press CTRL+SHIFT+ENTER to enter it as an array formula into all 10
cells.
Note that you don't enter it in the top and copy it down. That would just keep
returning the 1st result rather than all 10.
On Sat, 19 Feb 2005 14:25:02 -0800, George A. Yorks
<[email protected].(donotspam)> wrote:
>Thank you for all the help. I am experiencing one little problem. I write
>the formula into cell E1 and attempt to copy down to the last cell using the
>fill handle. I seems as though every second cell comes up with #N/A. Any
>ideas what is causing this.
>
>Again thank you for all the help
>
>"James" wrote:
>
>> You will need to use the vlookup formula. Have a look at
>> this in the help menu. It's quite simple, and compares to
>> lists to return a value.
>>
>> =vlookup(a1:a10,B1:c10,2,false)
>>
>> In this case, it looks up the values in cells a1 to a10
>> and compares them to the values in cells b1 to b10 and
>> returns the second value to thr right of these cells;
>> that is, the values in column c.
>>
>> Hope that helps.
>>
>> >-----Original 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
>> >.
>> >
>>
I don't understand the advantage to using an array formula in this
particular case.
Can anyone please explain to me the difference between:
=VLOOKUP(A1,$B$1:$C$10,2,0)
(Regular <Enter> - drag down to copy)
AND
=VLOOKUP($A$1:$A$10,$B$1:$C$10,2,0)
Array entered, <C,S,E>, where you have to first pre-select the rows, and
enter the formula in the top focus cell?
Am I missing something?
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Myrna Larson" <[email protected]> wrote in message
news:[email protected]...
> This is an array formula (since the 1st argument to VLOOKUP is not a
single
> cell, but 10 cells). I would also change the references to absolute, i.e.
>
> =VLOOKUP($A$1:$A$10,$B$1:$C$10,2,0)
>
> Then, to use this, select the 10 cells, E1:E10, and enter the above
formula in
> E1, and press CTRL+SHIFT+ENTER to enter it as an array formula into all 10
> cells.
>
> Note that you don't enter it in the top and copy it down. That would just
keep
> returning the 1st result rather than all 10.
>
>
> On Sat, 19 Feb 2005 14:25:02 -0800, George A. Yorks
> <[email protected].(donotspam)> wrote:
>
> >Thank you for all the help. I am experiencing one little problem. I
write
> >the formula into cell E1 and attempt to copy down to the last cell using
the
> >fill handle. I seems as though every second cell comes up with #N/A.
Any
> >ideas what is causing this.
> >
> >Again thank you for all the help
> >
> >"James" wrote:
> >
> >> You will need to use the vlookup formula. Have a look at
> >> this in the help menu. It's quite simple, and compares to
> >> lists to return a value.
> >>
> >> =vlookup(a1:a10,B1:c10,2,false)
> >>
> >> In this case, it looks up the values in cells a1 to a10
> >> and compares them to the values in cells b1 to b10 and
> >> returns the second value to thr right of these cells;
> >> that is, the values in column c.
> >>
> >> Hope that helps.
> >>
> >> >-----Original 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
> >> >.
> >> >
> >>
>
I'm not sure there is any advantage. My reply was targeted at the formula he
says he found in Help, in which the first argument was not a single cell, but
A1:A10. I haven't looked at it myself. Maybe he didn't understand the example
in Help and created the formula incorrectly.
On Sat, 19 Feb 2005 16:12:51 -0800, "Ragdyer" <[email protected]> wrote:
>I don't understand the advantage to using an array formula in this
>particular case.
>
>Can anyone please explain to me the difference between:
>
>=VLOOKUP(A1,$B$1:$C$10,2,0)
>(Regular <Enter> - drag down to copy)
>AND
>=VLOOKUP($A$1:$A$10,$B$1:$C$10,2,0)
>Array entered, <C,S,E>, where you have to first pre-select the rows, and
>enter the formula in the top focus cell?
>
>Am I missing something?
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" <[email protected].(donotspam)> wrote in message
> news:[email protected]...
> > 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
>
>
>
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" <[email protected].(donotspam)> wrote in message
news:[email protected]...
> 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" <[email protected].(donotspam)> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >
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" <[email protected].(donotspam)> wrote in message
> news:[email protected]...
> > 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" <[email protected].(donotspam)> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
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
<[email protected].(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" <[email protected].(donotspam)> wrote in message
>> > news:[email protected]...
>> > > 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
>> >
>> >
>> >
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" <[email protected].(donotspam)> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >
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
> <[email protected].(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" <[email protected].(donotspam)> wrote in message
> >> > news:[email protected]...
> >> > > 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
> >> >
> >> >
> >> >
>
>
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" <[email protected].(donotspam)> wrote in message
news:[email protected]...
> 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" <[email protected].(donotspam)> wrote in message
>> news:[email protected]...
>> > 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" <[email protected].(donotspam)> wrote in message
>> > > news:[email protected]...
>> > > > 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
>> > >
>> > >
>> > >
>>
>>
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" <[email protected].(donotspam)> wrote in message
> news:[email protected]...
> > 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" <[email protected].(donotspam)> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected].(donotspam)> wrote in message
> >> > > news:[email protected]...
> >> > > > 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
> >> > >
> >> > >
> >> > >
> >>
> >>
>
>
>
First question:
Replace error message with a null ( "" ), which can then be added:
=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 Group, so all may benefit!
==============================================
"George A. Yorks" <[email protected].(donotspam)> wrote in message
news:[email protected]...
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" <[email protected].(donotspam)> wrote in message
> news:[email protected]...
> > 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" <[email protected].(donotspam)> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected].(donotspam)> wrote in message
> >> > > news:[email protected]...
> >> > > > 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
> >> > >
> >> > >
> >> > >
> >>
> >>
>
>
>
I tried using the formula below<from =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)),"",VLOOKUP(A1,
> 'Sheet2'!$A$1:$D$10, 4, 0)
> --
>
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
> "George A. Yorks" <[email protected].(donotspam)> wrote in message
> news:[email protected]...
> 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" <[email protected].(donotspam)> wrote in message
> > news:[email protected]...
> > > 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" <[email protected].(donotspam)> wrote in message
> > >> news:[email protected]...
> > >> > 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" <[email protected].(donotspam)> wrote in message
> > >> > > news:[email protected]...
> > >> > > > 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
> > >> > >
> > >> > >
> > >> > >
> > >>
> > >>
> >
> >
> >
>
>
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" <[email protected].(donotspam)> wrote in message
news:[email protected]...
> I tried using the formula below<from =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)),"",VLOOKUP(A1,
> > 'Sheet2'!$A$1:$D$10, 4, 0)
> > --
> >
> > HTH,
> >
> > RD
> > ==============================================
> > Please keep all correspondence within the Group, so all may benefit!
> > ==============================================
> >
> >
> > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > news:[email protected]...
> > 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" <[email protected].(donotspam)> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected].(donotspam)> wrote in message
> > > >> news:[email protected]...
> > > >> > 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" <[email protected].(donotspam)> wrote in
message
> > > >> > > news:[email protected]...
> > > >> > > > 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
> > > >> > >
> > > >> > >
> > > >> > >
> > > >>
> > > >>
> > >
> > >
> > >
> >
> >
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" <[email protected].(donotspam)> wrote in message
> news:[email protected]...
> > I tried using the formula below<from =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)),"",VLOOKUP(A1,
> > > 'Sheet2'!$A$1:$D$10, 4, 0)
> > > --
> > >
> > > HTH,
> > >
> > > RD
> > > ==============================================
> > > Please keep all correspondence within the Group, so all may benefit!
> > > ==============================================
> > >
> > >
> > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > news:[email protected]...
> > > 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" <[email protected].(donotspam)> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected].(donotspam)> wrote in message
> > > > >> news:[email protected]...
> > > > >> > 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" <[email protected].(donotspam)> wrote in
> message
> > > > >> > > news:[email protected]...
> > > > >> > > > 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
> > > > >> > >
> > > > >> > >
> > > > >> > >
> > > > >>
> > > > >>
> > > >
> > > >
> > > >
> > >
> > >
>
>
I understand that excel is not seeing a match. But if I do the following the
file is recognized. I copy the file(containing a column of names and column
of numbers) and past to excel.I then copy from excel to lotus 123 and then
from lotus 123 back to excel. Is there any reason for this and is there any
other, less cumbersome, proceedure???
"George A. Yorks" wrote:
> 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
> > <[email protected].(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" <[email protected].(donotspam)> wrote in message
> > >> > news:[email protected]...
> > >> > > 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
> > >> >
> > >> >
> > >> >
> >
> >
Thanks for the help. I am confronted with a new scenario which I'll present
for help.
One worksheet titled geo I have a list of names b4 thru b18 and data in
k4thru k18. I want to transfere the data to worksheet titled miriam after
searching for the exact names. The names of the second worksheet are column
ax3 thru ax89. Hope this makes sense to you.
George
"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)),"",VLOOKUP(A1,
> 'Sheet2'!$A$1:$D$10, 4, 0)
> --
>
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
> "George A. Yorks" <[email protected].(donotspam)> wrote in message
> news:[email protected]...
> 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" <[email protected].(donotspam)> wrote in message
> > news:[email protected]...
> > > 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" <[email protected].(donotspam)> wrote in message
> > >> news:[email protected]...
> > >> > 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" <[email protected].(donotspam)> wrote in message
> > >> > > news:[email protected]...
> > >> > > > 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
> > >> > >
> > >> > >
> > >> > >
> > >>
> > >>
> >
> >
> >
>
>
Try this in Ay3 of "Miriam":
=IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$18,0)))
You can then *double click* on the "fill handle" in the lower right corner
of AY3, which will *automatically* copy the formula in AY3 down Column AY,
as far as there is data in Column AX.
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
"George A. Yorks" <[email protected].(donotspam)> wrote in message
news:[email protected]...
Thanks for the help. I am confronted with a new scenario which I'll present
for help.
One worksheet titled geo I have a list of names b4 thru b18 and data in
k4thru k18. I want to transfere the data to worksheet titled miriam after
searching for the exact names. The names of the second worksheet are column
ax3 thru ax89. Hope this makes sense to you.
George
"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)),"",VLOOKUP(A1,
> 'Sheet2'!$A$1:$D$10, 4, 0)
> --
>
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
> "George A. Yorks" <[email protected].(donotspam)> wrote in message
> news:[email protected]...
> 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" <[email protected].(donotspam)> wrote in message
> > news:[email protected]...
> > > 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" <[email protected].(donotspam)> wrote in message
> > >> news:[email protected]...
> > >> > 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" <[email protected].(donotspam)> wrote in
message
> > >> > > news:[email protected]...
> > >> > > > 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
> > >> > >
> > >> > >
> > >> > >
> > >>
> > >>
> >
> >
> >
>
>
I tried this formula and get a "not found" also this is how the formula gets
entered. Don't know why. It was entered as below.
=IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)))
"RagDyeR" wrote:
> Try this in Ay3 of "Miriam":
>
> =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
> Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$18,0)))
>
> You can then *double click* on the "fill handle" in the lower right corner
> of AY3, which will *automatically* copy the formula in AY3 down Column AY,
> as far as there is data in Column AX.
> --
>
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
> "George A. Yorks" <[email protected].(donotspam)> wrote in message
> news:[email protected]...
> Thanks for the help. I am confronted with a new scenario which I'll present
> for help.
>
> One worksheet titled geo I have a list of names b4 thru b18 and data in
> k4thru k18. I want to transfere the data to worksheet titled miriam after
> searching for the exact names. The names of the second worksheet are column
> ax3 thru ax89. Hope this makes sense to you.
>
> George
>
> "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)),"",VLOOKUP(A1,
> > 'Sheet2'!$A$1:$D$10, 4, 0)
> > --
> >
> > HTH,
> >
> > RD
> > ==============================================
> > Please keep all correspondence within the Group, so all may benefit!
> > ==============================================
> >
> >
> > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > news:[email protected]...
> > 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" <[email protected].(donotspam)> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected].(donotspam)> wrote in message
> > > >> news:[email protected]...
> > > >> > 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" <[email protected].(donotspam)> wrote in
> message
> > > >> > > news:[email protected]...
> > > >> > > > 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
> > > >> > >
> > > >> > >
> > > >> > >
> > > >>
> > > >>
> > >
> > >
> > >
> >
> >
>
>
>
I would guess that perhaps the data that's in AX3 doesn't *exactly* match
what's in Column B.
Do you import any of your data?
Are the names "full" names, first, and/or middle and last names, where there
might be a possibilty that the spaces between them might not be a normal
Char(32) space?
Could there be a possibility of leading and/or trailing spaces?
For a test, key a name into Column B.
Enter that same name in *exactly* the same way into AX3, and see if you get
a correct return from your formula.
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------
"George A. Yorks" <[email protected].(donotspam)> wrote in message
news:[email protected]...
I tried this formula and get a "not found" also this is how the formula gets
entered. Don't know why. It was entered as below.
=IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18
,0)))
"RagDyeR" wrote:
> Try this in Ay3 of "Miriam":
>
> =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
> Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$18,0)))
>
> You can then *double click* on the "fill handle" in the lower right corner
> of AY3, which will *automatically* copy the formula in AY3 down Column AY,
> as far as there is data in Column AX.
> --
>
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
> "George A. Yorks" <[email protected].(donotspam)> wrote in message
> news:[email protected]...
> Thanks for the help. I am confronted with a new scenario which I'll
present
> for help.
>
> One worksheet titled geo I have a list of names b4 thru b18 and data in
> k4thru k18. I want to transfere the data to worksheet titled miriam after
> searching for the exact names. The names of the second worksheet are
column
> ax3 thru ax89. Hope this makes sense to you.
>
> George
>
> "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)),"",VLOOKUP(A1,
> > 'Sheet2'!$A$1:$D$10, 4, 0)
> > --
> >
> > HTH,
> >
> > RD
> > ==============================================
> > Please keep all correspondence within the Group, so all may benefit!
> > ==============================================
> >
> >
> > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > news:[email protected]...
> > 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" <[email protected].(donotspam)> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected].(donotspam)> wrote in message
> > > >> news:[email protected]...
> > > >> > 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" <[email protected].(donotspam)> wrote in
> message
> > > >> > > news:[email protected]...
> > > >> > > > 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
> > > >> > >
> > > >> > >
> > > >> > >
> > > >>
> > > >>
> > >
> > >
> > >
> >
> >
>
>
>
In using the lookup formula I find that the data in column AX3 is not exactly
like that in sheet 2 B4. ie: the spacing between first and last name is not
the same. Any way to correct this. I only know of the differences when the
dollar amounts are not tranfered. I then have to correct these errors
manually. Hope this makes sense
Thanks for all the help
"George A. Yorks" wrote:
> I tried this formula and get a "not found" also this is how the formula gets
> entered. Don't know why. It was entered as below.
> =IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
> found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)))
> "RagDyeR" wrote:
>
> > Try this in Ay3 of "Miriam":
> >
> > =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
> > Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$18,0)))
> >
> > You can then *double click* on the "fill handle" in the lower right corner
> > of AY3, which will *automatically* copy the formula in AY3 down Column AY,
> > as far as there is data in Column AX.
> > --
> >
> > HTH,
> >
> > RD
> > ==============================================
> > Please keep all correspondence within the Group, so all may benefit!
> > ==============================================
> >
> >
> > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > news:[email protected]...
> > Thanks for the help. I am confronted with a new scenario which I'll present
> > for help.
> >
> > One worksheet titled geo I have a list of names b4 thru b18 and data in
> > k4thru k18. I want to transfere the data to worksheet titled miriam after
> > searching for the exact names. The names of the second worksheet are column
> > ax3 thru ax89. Hope this makes sense to you.
> >
> > George
> >
> > "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)),"",VLOOKUP(A1,
> > > 'Sheet2'!$A$1:$D$10, 4, 0)
> > > --
> > >
> > > HTH,
> > >
> > > RD
> > > ==============================================
> > > Please keep all correspondence within the Group, so all may benefit!
> > > ==============================================
> > >
> > >
> > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > news:[email protected]...
> > > 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" <[email protected].(donotspam)> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected].(donotspam)> wrote in message
> > > > >> news:[email protected]...
> > > > >> > 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" <[email protected].(donotspam)> wrote in
> > message
> > > > >> > > news:[email protected]...
> > > > >> > > > 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
> > > > >> > >
> > > > >> > >
> > > > >> > >
> > > > >>
> > > > >>
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >
If you sometimes have double or triple spaces, you can do a search and replace
on column AX: search for two spaces and replace with 1 space. Repeat until
Excel tell you there are no more matches.
On Mon, 7 Mar 2005 20:17:02 -0800, George A. Yorks
<[email protected].(donotspam)> wrote:
>In using the lookup formula I find that the data in column AX3 is not exactly
>like that in sheet 2 B4. ie: the spacing between first and last name is not
>the same. Any way to correct this. I only know of the differences when the
>dollar amounts are not tranfered. I then have to correct these errors
>manually. Hope this makes sense
>
>Thanks for all the help
>
>"George A. Yorks" wrote:
>
>> I tried this formula and get a "not found" also this is how the formula
gets
>> entered. Don't know why. It was entered as below.
>> =IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
>>
found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)))
>> "RagDyeR" wrote:
>>
>> > Try this in Ay3 of "Miriam":
>> >
>> > =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
>> > Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$18,0)))
>> >
>> > You can then *double click* on the "fill handle" in the lower right
corner
>> > of AY3, which will *automatically* copy the formula in AY3 down Column
AY,
>> > as far as there is data in Column AX.
>> > --
>> >
>> > HTH,
>> >
>> > RD
>> > ==============================================
>> > Please keep all correspondence within the Group, so all may benefit!
>> > ==============================================
>> >
>> >
>> > "George A. Yorks" <[email protected].(donotspam)> wrote in message
>> > news:[email protected]...
>> > Thanks for the help. I am confronted with a new scenario which I'll
present
>> > for help.
>> >
>> > One worksheet titled geo I have a list of names b4 thru b18 and data in
>> > k4thru k18. I want to transfere the data to worksheet titled miriam
after
>> > searching for the exact names. The names of the second worksheet are
column
>> > ax3 thru ax89. Hope this makes sense to you.
>> >
>> > George
>> >
>> > "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)),"",VLOOKUP(A1,
>> > > 'Sheet2'!$A$1:$D$10, 4, 0)
>> > > --
>> > >
>> > > HTH,
>> > >
>> > > RD
>> > > ==============================================
>> > > Please keep all correspondence within the Group, so all may benefit!
>> > > ==============================================
>> > >
>> > >
>> > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
>> > > news:[email protected]...
>> > > 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" <[email protected].(donotspam)> wrote in message
>> > > > news:[email protected]...
>> > > > > 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" <[email protected].(donotspam)> wrote in
message
>> > > > >> news:[email protected]...
>> > > > >> > 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" <[email protected].(donotspam)> wrote in
>> > message
>> > > > >> > > news:[email protected]...
>> > > > >> > > > 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
>> > > > >> > >
>> > > > >> > >
>> > > > >> > >
>> > > > >>
>> > > > >>
>> > > >
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>> >
Have looked through help menu for how to perform a search and replace you
speak of. I can't find anything. Could you give me some direction. Thanks
for all your help
"Myrna Larson" wrote:
> If you sometimes have double or triple spaces, you can do a search and replace
> on column AX: search for two spaces and replace with 1 space. Repeat until
> Excel tell you there are no more matches.
>
> On Mon, 7 Mar 2005 20:17:02 -0800, George A. Yorks
> <[email protected].(donotspam)> wrote:
>
> >In using the lookup formula I find that the data in column AX3 is not exactly
> >like that in sheet 2 B4. ie: the spacing between first and last name is not
> >the same. Any way to correct this. I only know of the differences when the
> >dollar amounts are not tranfered. I then have to correct these errors
> >manually. Hope this makes sense
> >
> >Thanks for all the help
> >
> >"George A. Yorks" wrote:
> >
> >> I tried this formula and get a "not found" also this is how the formula
> gets
> >> entered. Don't know why. It was entered as below.
> >> =IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
> >>
> found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)))
> >> "RagDyeR" wrote:
> >>
> >> > Try this in Ay3 of "Miriam":
> >> >
> >> > =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
> >> > Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$18,0)))
> >> >
> >> > You can then *double click* on the "fill handle" in the lower right
> corner
> >> > of AY3, which will *automatically* copy the formula in AY3 down Column
> AY,
> >> > as far as there is data in Column AX.
> >> > --
> >> >
> >> > HTH,
> >> >
> >> > RD
> >> > ==============================================
> >> > Please keep all correspondence within the Group, so all may benefit!
> >> > ==============================================
> >> >
> >> >
> >> > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> >> > news:[email protected]...
> >> > Thanks for the help. I am confronted with a new scenario which I'll
> present
> >> > for help.
> >> >
> >> > One worksheet titled geo I have a list of names b4 thru b18 and data in
> >> > k4thru k18. I want to transfere the data to worksheet titled miriam
> after
> >> > searching for the exact names. The names of the second worksheet are
> column
> >> > ax3 thru ax89. Hope this makes sense to you.
> >> >
> >> > George
> >> >
> >> > "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)),"",VLOOKUP(A1,
> >> > > 'Sheet2'!$A$1:$D$10, 4, 0)
> >> > > --
> >> > >
> >> > > HTH,
> >> > >
> >> > > RD
> >> > > ==============================================
> >> > > Please keep all correspondence within the Group, so all may benefit!
> >> > > ==============================================
> >> > >
> >> > >
> >> > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> >> > > news:[email protected]...
> >> > > 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" <[email protected].(donotspam)> wrote in message
> >> > > > news:[email protected]...
> >> > > > > 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" <[email protected].(donotspam)> wrote in
> message
> >> > > > >> news:[email protected]...
> >> > > > >> > 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" <[email protected].(donotspam)> wrote in
> >> > message
> >> > > > >> > > news:[email protected]...
> >> > > > >> > > > 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
> >> > > > >> > >
> >> > > > >> > >
> >> > > > >> > >
> >> > > > >>
> >> > > > >>
> >> > > >
> >> > > >
> >> > > >
> >> > >
> >> > >
> >> >
> >> >
> >> >
>
>
Look under Edit|Replace on the worksheet toolbar.
George A. Yorks wrote:
>
> Have looked through help menu for how to perform a search and replace you
> speak of. I can't find anything. Could you give me some direction. Thanks
> for all your help
>
> "Myrna Larson" wrote:
>
> > If you sometimes have double or triple spaces, you can do a search and replace
> > on column AX: search for two spaces and replace with 1 space. Repeat until
> > Excel tell you there are no more matches.
> >
> > On Mon, 7 Mar 2005 20:17:02 -0800, George A. Yorks
> > <[email protected].(donotspam)> wrote:
> >
> > >In using the lookup formula I find that the data in column AX3 is not exactly
> > >like that in sheet 2 B4. ie: the spacing between first and last name is not
> > >the same. Any way to correct this. I only know of the differences when the
> > >dollar amounts are not tranfered. I then have to correct these errors
> > >manually. Hope this makes sense
> > >
> > >Thanks for all the help
> > >
> > >"George A. Yorks" wrote:
> > >
> > >> I tried this formula and get a "not found" also this is how the formula
> > gets
> > >> entered. Don't know why. It was entered as below.
> > >> =IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
> > >>
> > found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)))
> > >> "RagDyeR" wrote:
> > >>
> > >> > Try this in Ay3 of "Miriam":
> > >> >
> > >> > =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
> > >> > Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$18,0)))
> > >> >
> > >> > You can then *double click* on the "fill handle" in the lower right
> > corner
> > >> > of AY3, which will *automatically* copy the formula in AY3 down Column
> > AY,
> > >> > as far as there is data in Column AX.
> > >> > --
> > >> >
> > >> > HTH,
> > >> >
> > >> > RD
> > >> > ==============================================
> > >> > Please keep all correspondence within the Group, so all may benefit!
> > >> > ==============================================
> > >> >
> > >> >
> > >> > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > >> > news:[email protected]...
> > >> > Thanks for the help. I am confronted with a new scenario which I'll
> > present
> > >> > for help.
> > >> >
> > >> > One worksheet titled geo I have a list of names b4 thru b18 and data in
> > >> > k4thru k18. I want to transfere the data to worksheet titled miriam
> > after
> > >> > searching for the exact names. The names of the second worksheet are
> > column
> > >> > ax3 thru ax89. Hope this makes sense to you.
> > >> >
> > >> > George
> > >> >
> > >> > "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)),"",VLOOKUP(A1,
> > >> > > 'Sheet2'!$A$1:$D$10, 4, 0)
> > >> > > --
> > >> > >
> > >> > > HTH,
> > >> > >
> > >> > > RD
> > >> > > ==============================================
> > >> > > Please keep all correspondence within the Group, so all may benefit!
> > >> > > ==============================================
> > >> > >
> > >> > >
> > >> > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > >> > > news:[email protected]...
> > >> > > 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" <[email protected].(donotspam)> wrote in message
> > >> > > > news:[email protected]...
> > >> > > > > 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" <[email protected].(donotspam)> wrote in
> > message
> > >> > > > >> news:[email protected]...
> > >> > > > >> > 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" <[email protected].(donotspam)> wrote in
> > >> > message
> > >> > > > >> > > news:[email protected]...
> > >> > > > >> > > > 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
> > >> > > > >> > >
> > >> > > > >> > >
> > >> > > > >> > >
> > >> > > > >>
> > >> > > > >>
> > >> > > >
> > >> > > >
> > >> > > >
> > >> > >
> > >> > >
> > >> >
> > >> >
> > >> >
> >
> >
--
Dave Peterson
My column is comprised of names with varied spaces between first and last
name.As you suggested went to edit find/replace and in the find place the
name with extra spaces. in the replace entered the name with one place and
clicked on find. Nothing. The help menu is of no help. Has to be
something obvious that I am not doing
"Dave Peterson" wrote:
> Look under Edit|Replace on the worksheet toolbar.
>
> George A. Yorks wrote:
> >
> > Have looked through help menu for how to perform a search and replace you
> > speak of. I can't find anything. Could you give me some direction. Thanks
> > for all your help
> >
> > "Myrna Larson" wrote:
> >
> > > If you sometimes have double or triple spaces, you can do a search and replace
> > > on column AX: search for two spaces and replace with 1 space. Repeat until
> > > Excel tell you there are no more matches.
> > >
> > > On Mon, 7 Mar 2005 20:17:02 -0800, George A. Yorks
> > > <[email protected].(donotspam)> wrote:
> > >
> > > >In using the lookup formula I find that the data in column AX3 is not exactly
> > > >like that in sheet 2 B4. ie: the spacing between first and last name is not
> > > >the same. Any way to correct this. I only know of the differences when the
> > > >dollar amounts are not tranfered. I then have to correct these errors
> > > >manually. Hope this makes sense
> > > >
> > > >Thanks for all the help
> > > >
> > > >"George A. Yorks" wrote:
> > > >
> > > >> I tried this formula and get a "not found" also this is how the formula
> > > gets
> > > >> entered. Don't know why. It was entered as below.
> > > >> =IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
> > > >>
> > > found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)))
> > > >> "RagDyeR" wrote:
> > > >>
> > > >> > Try this in Ay3 of "Miriam":
> > > >> >
> > > >> > =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
> > > >> > Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$18,0)))
> > > >> >
> > > >> > You can then *double click* on the "fill handle" in the lower right
> > > corner
> > > >> > of AY3, which will *automatically* copy the formula in AY3 down Column
> > > AY,
> > > >> > as far as there is data in Column AX.
> > > >> > --
> > > >> >
> > > >> > HTH,
> > > >> >
> > > >> > RD
> > > >> > ==============================================
> > > >> > Please keep all correspondence within the Group, so all may benefit!
> > > >> > ==============================================
> > > >> >
> > > >> >
> > > >> > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > >> > news:[email protected]...
> > > >> > Thanks for the help. I am confronted with a new scenario which I'll
> > > present
> > > >> > for help.
> > > >> >
> > > >> > One worksheet titled geo I have a list of names b4 thru b18 and data in
> > > >> > k4thru k18. I want to transfere the data to worksheet titled miriam
> > > after
> > > >> > searching for the exact names. The names of the second worksheet are
> > > column
> > > >> > ax3 thru ax89. Hope this makes sense to you.
> > > >> >
> > > >> > George
> > > >> >
> > > >> > "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)),"",VLOOKUP(A1,
> > > >> > > 'Sheet2'!$A$1:$D$10, 4, 0)
> > > >> > > --
> > > >> > >
> > > >> > > HTH,
> > > >> > >
> > > >> > > RD
> > > >> > > ==============================================
> > > >> > > Please keep all correspondence within the Group, so all may benefit!
> > > >> > > ==============================================
> > > >> > >
> > > >> > >
> > > >> > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > >> > > news:[email protected]...
> > > >> > > 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" <[email protected].(donotspam)> wrote in message
> > > >> > > > news:[email protected]...
> > > >> > > > > 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" <[email protected].(donotspam)> wrote in
> > > message
> > > >> > > > >> news:[email protected]...
> > > >> > > > >> > 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" <[email protected].(donotspam)> wrote in
> > > >> > message
> > > >> > > > >> > > news:[email protected]...
> > > >> > > > >> > > > 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
> > > >> > > > >> > >
> > > >> > > > >> > >
> > > >> > > > >> > >
> > > >> > > > >>
> > > >> > > > >>
> > > >> > > >
> > > >> > > >
> > > >> > > >
> > > >> > >
> > > >> > >
> > > >> >
> > > >> >
> > > >> >
> > >
> > >
>
> --
>
> Dave Peterson
>
You can get rid of leading/trailing/duplicate internal spaces by using a helper
column with a formula like:
=trim(a1)
copy down the column.
Then you can copy|paste special|values right over the original list and delete
the helper column. (Do this on the lookup table, too.)
If you copied from a web page, maybe you're seeing the non-breaking HTML spaces.
David McRitchie has a routine that will clean up this kind of stuff at:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")
George A. Yorks wrote:
>
> My column is comprised of names with varied spaces between first and last
> name.As you suggested went to edit find/replace and in the find place the
> name with extra spaces. in the replace entered the name with one place and
> clicked on find. Nothing. The help menu is of no help. Has to be
> something obvious that I am not doing
>
> "Dave Peterson" wrote:
>
> > Look under Edit|Replace on the worksheet toolbar.
> >
> > George A. Yorks wrote:
> > >
> > > Have looked through help menu for how to perform a search and replace you
> > > speak of. I can't find anything. Could you give me some direction. Thanks
> > > for all your help
> > >
> > > "Myrna Larson" wrote:
> > >
> > > > If you sometimes have double or triple spaces, you can do a search and replace
> > > > on column AX: search for two spaces and replace with 1 space. Repeat until
> > > > Excel tell you there are no more matches.
> > > >
> > > > On Mon, 7 Mar 2005 20:17:02 -0800, George A. Yorks
> > > > <[email protected].(donotspam)> wrote:
> > > >
> > > > >In using the lookup formula I find that the data in column AX3 is not exactly
> > > > >like that in sheet 2 B4. ie: the spacing between first and last name is not
> > > > >the same. Any way to correct this. I only know of the differences when the
> > > > >dollar amounts are not tranfered. I then have to correct these errors
> > > > >manually. Hope this makes sense
> > > > >
> > > > >Thanks for all the help
> > > > >
> > > > >"George A. Yorks" wrote:
> > > > >
> > > > >> I tried this formula and get a "not found" also this is how the formula
> > > > gets
> > > > >> entered. Don't know why. It was entered as below.
> > > > >> =IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
> > > > >>
> > > > found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)))
> > > > >> "RagDyeR" wrote:
> > > > >>
> > > > >> > Try this in Ay3 of "Miriam":
> > > > >> >
> > > > >> > =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
> > > > >> > Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$18,0)))
> > > > >> >
> > > > >> > You can then *double click* on the "fill handle" in the lower right
> > > > corner
> > > > >> > of AY3, which will *automatically* copy the formula in AY3 down Column
> > > > AY,
> > > > >> > as far as there is data in Column AX.
> > > > >> > --
> > > > >> >
> > > > >> > HTH,
> > > > >> >
> > > > >> > RD
> > > > >> > ==============================================
> > > > >> > Please keep all correspondence within the Group, so all may benefit!
> > > > >> > ==============================================
> > > > >> >
> > > > >> >
> > > > >> > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > > >> > news:[email protected]...
> > > > >> > Thanks for the help. I am confronted with a new scenario which I'll
> > > > present
> > > > >> > for help.
> > > > >> >
> > > > >> > One worksheet titled geo I have a list of names b4 thru b18 and data in
> > > > >> > k4thru k18. I want to transfere the data to worksheet titled miriam
> > > > after
> > > > >> > searching for the exact names. The names of the second worksheet are
> > > > column
> > > > >> > ax3 thru ax89. Hope this makes sense to you.
> > > > >> >
> > > > >> > George
> > > > >> >
> > > > >> > "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)),"",VLOOKUP(A1,
> > > > >> > > 'Sheet2'!$A$1:$D$10, 4, 0)
> > > > >> > > --
> > > > >> > >
> > > > >> > > HTH,
> > > > >> > >
> > > > >> > > RD
> > > > >> > > ==============================================
> > > > >> > > Please keep all correspondence within the Group, so all may benefit!
> > > > >> > > ==============================================
> > > > >> > >
> > > > >> > >
> > > > >> > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > > >> > > news:[email protected]...
> > > > >> > > 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" <[email protected].(donotspam)> wrote in message
> > > > >> > > > news:[email protected]...
> > > > >> > > > > 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" <[email protected].(donotspam)> wrote in
> > > > message
> > > > >> > > > >> news:[email protected]...
> > > > >> > > > >> > 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" <[email protected].(donotspam)> wrote in
> > > > >> > message
> > > > >> > > > >> > > news:[email protected]...
> > > > >> > > > >> > > > 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
> > > > >> > > > >> > >
> > > > >> > > > >> > >
> > > > >> > > > >> > >
> > > > >> > > > >>
> > > > >> > > > >>
> > > > >> > > >
> > > > >> > > >
> > > > >> > > >
> > > > >> > >
> > > > >> > >
> > > > >> >
> > > > >> >
> > > > >> >
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >
--
Dave Peterson
I am still not able to adjust space between two words. I have a column of
names with two or three spaces between first and last name. I want to adjust
so there is only one space between the two names. How to do this. Was
suggested I do a search and replace but have not found how to do this. Any
help appreciated
--
George
"George A. Yorks" wrote:
> Have looked through help menu for how to perform a search and replace you
> speak of. I can't find anything. Could you give me some direction. Thanks
> for all your help
>
> "Myrna Larson" wrote:
>
> > If you sometimes have double or triple spaces, you can do a search and replace
> > on column AX: search for two spaces and replace with 1 space. Repeat until
> > Excel tell you there are no more matches.
> >
> > On Mon, 7 Mar 2005 20:17:02 -0800, George A. Yorks
> > <[email protected].(donotspam)> wrote:
> >
> > >In using the lookup formula I find that the data in column AX3 is not exactly
> > >like that in sheet 2 B4. ie: the spacing between first and last name is not
> > >the same. Any way to correct this. I only know of the differences when the
> > >dollar amounts are not tranfered. I then have to correct these errors
> > >manually. Hope this makes sense
> > >
> > >Thanks for all the help
> > >
> > >"George A. Yorks" wrote:
> > >
> > >> I tried this formula and get a "not found" also this is how the formula
> > gets
> > >> entered. Don't know why. It was entered as below.
> > >> =IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
> > >>
> > found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)))
> > >> "RagDyeR" wrote:
> > >>
> > >> > Try this in Ay3 of "Miriam":
> > >> >
> > >> > =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
> > >> > Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$18,0)))
> > >> >
> > >> > You can then *double click* on the "fill handle" in the lower right
> > corner
> > >> > of AY3, which will *automatically* copy the formula in AY3 down Column
> > AY,
> > >> > as far as there is data in Column AX.
> > >> > --
> > >> >
> > >> > HTH,
> > >> >
> > >> > RD
> > >> > ==============================================
> > >> > Please keep all correspondence within the Group, so all may benefit!
> > >> > ==============================================
> > >> >
> > >> >
> > >> > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > >> > news:[email protected]...
> > >> > Thanks for the help. I am confronted with a new scenario which I'll
> > present
> > >> > for help.
> > >> >
> > >> > One worksheet titled geo I have a list of names b4 thru b18 and data in
> > >> > k4thru k18. I want to transfere the data to worksheet titled miriam
> > after
> > >> > searching for the exact names. The names of the second worksheet are
> > column
> > >> > ax3 thru ax89. Hope this makes sense to you.
> > >> >
> > >> > George
> > >> >
> > >> > "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)),"",VLOOKUP(A1,
> > >> > > 'Sheet2'!$A$1:$D$10, 4, 0)
> > >> > > --
> > >> > >
> > >> > > HTH,
> > >> > >
> > >> > > RD
> > >> > > ==============================================
> > >> > > Please keep all correspondence within the Group, so all may benefit!
> > >> > > ==============================================
> > >> > >
> > >> > >
> > >> > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > >> > > news:[email protected]...
> > >> > > 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" <[email protected].(donotspam)> wrote in message
> > >> > > > news:[email protected]...
> > >> > > > > 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" <[email protected].(donotspam)> wrote in
> > message
> > >> > > > >> news:[email protected]...
> > >> > > > >> > 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" <[email protected].(donotspam)> wrote in
> > >> > message
> > >> > > > >> > > news:[email protected]...
> > >> > > > >> > > > 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
> > >> > > > >> > >
> > >> > > > >> > >
> > >> > > > >> > >
> > >> > > > >>
> > >> > > > >>
> > >> > > >
> > >> > > >
> > >> > > >
> > >> > >
> > >> > >
> > >> >
> > >> >
> > >> >
> >
> >
George,
you might have some odd characters in there, that look like spaces.
Try this
- select a cell with the multiple gaps
- hit F2 then in the formula bar select 2 of those characters and copy (to
the clipboard) then exit
- Find and Replace (Ctrl H)
- paste the clipboard value into the Find What box
- put a single space in the Replace with
- OK
repeat this until none found, then try replacing two spaces with one until
no more.
--
HTH
Bob Phillips
"George A. Yorks" <[email protected].(donotspam)> wrote in message
news:[email protected]...
> I am still not able to adjust space between two words. I have a column of
> names with two or three spaces between first and last name. I want to
adjust
> so there is only one space between the two names. How to do this. Was
> suggested I do a search and replace but have not found how to do this.
Any
> help appreciated
> --
> George
>
>
> "George A. Yorks" wrote:
>
> > Have looked through help menu for how to perform a search and replace
you
> > speak of. I can't find anything. Could you give me some direction.
Thanks
> > for all your help
> >
> > "Myrna Larson" wrote:
> >
> > > If you sometimes have double or triple spaces, you can do a search and
replace
> > > on column AX: search for two spaces and replace with 1 space. Repeat
until
> > > Excel tell you there are no more matches.
> > >
> > > On Mon, 7 Mar 2005 20:17:02 -0800, George A. Yorks
> > > <[email protected].(donotspam)> wrote:
> > >
> > > >In using the lookup formula I find that the data in column AX3 is not
exactly
> > > >like that in sheet 2 B4. ie: the spacing between first and last name
is not
> > > >the same. Any way to correct this. I only know of the differences
when the
> > > >dollar amounts are not tranfered. I then have to correct these
errors
> > > >manually. Hope this makes sense
> > > >
> > > >Thanks for all the help
> > > >
> > > >"George A. Yorks" wrote:
> > > >
> > > >> I tried this formula and get a "not found" also this is how the
formula
> > > gets
> > > >> entered. Don't know why. It was entered as below.
> > > >> =IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
> > > >>
> > >
found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18
,0)))
> > > >> "RagDyeR" wrote:
> > > >>
> > > >> > Try this in Ay3 of "Miriam":
> > > >> >
> > > >> > =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
> > > >> > Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$18,0)))
> > > >> >
> > > >> > You can then *double click* on the "fill handle" in the lower
right
> > > corner
> > > >> > of AY3, which will *automatically* copy the formula in AY3 down
Column
> > > AY,
> > > >> > as far as there is data in Column AX.
> > > >> > --
> > > >> >
> > > >> > HTH,
> > > >> >
> > > >> > RD
> > > >> > ==============================================
> > > >> > Please keep all correspondence within the Group, so all may
benefit!
> > > >> > ==============================================
> > > >> >
> > > >> >
> > > >> > "George A. Yorks" <[email protected].(donotspam)> wrote in
message
> > > >> > news:[email protected]...
> > > >> > Thanks for the help. I am confronted with a new scenario which
I'll
> > > present
> > > >> > for help.
> > > >> >
> > > >> > One worksheet titled geo I have a list of names b4 thru b18 and
data in
> > > >> > k4thru k18. I want to transfere the data to worksheet titled
miriam
> > > after
> > > >> > searching for the exact names. The names of the second worksheet
are
> > > column
> > > >> > ax3 thru ax89. Hope this makes sense to you.
> > > >> >
> > > >> > George
> > > >> >
> > > >> > "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)),"",VLOOKUP(A1,
> > > >> > > 'Sheet2'!$A$1:$D$10, 4, 0)
> > > >> > > --
> > > >> > >
> > > >> > > HTH,
> > > >> > >
> > > >> > > RD
> > > >> > > ==============================================
> > > >> > > Please keep all correspondence within the Group, so all may
benefit!
> > > >> > > ==============================================
> > > >> > >
> > > >> > >
> > > >> > > "George A. Yorks" <[email protected].(donotspam)> wrote in
message
> > > >> > > news:[email protected]...
> > > >> > > 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" <[email protected].(donotspam)> wrote in
message
> > > >> > > > news:[email protected]...
> > > >> > > > > 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" <[email protected].(donotspam)> wrote
in
> > > message
> > > >> > > > >> news:[email protected]...
> > > >> > > > >> > 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" <[email protected].(donotspam)>
wrote in
> > > >> > message
> > > >> > > > >> > >
news:[email protected]...
> > > >> > > > >> > > > 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
> > > >> > > > >> > >
> > > >> > > > >> > >
> > > >> > > > >> > >
> > > >> > > > >>
> > > >> > > > >>
> > > >> > > >
> > > >> > > >
> > > >> > > >
> > > >> > >
> > > >> > >
> > > >> >
> > > >> >
> > > >> >
> > >
> > >
In using find and replace, I select the full column do ctrl. h to get find
and replace but still trying to figure how to look for three spaces in find
and two spaces in replace. If I use * * that is all that gets replaced
--
George
"George A. Yorks" wrote:
> I am still not able to adjust space between two words. I have a column of
> names with two or three spaces between first and last name. I want to adjust
> so there is only one space between the two names. How to do this. Was
> suggested I do a search and replace but have not found how to do this. Any
> help appreciated
> --
> George
>
>
> "George A. Yorks" wrote:
>
> > Have looked through help menu for how to perform a search and replace you
> > speak of. I can't find anything. Could you give me some direction. Thanks
> > for all your help
> >
> > "Myrna Larson" wrote:
> >
> > > If you sometimes have double or triple spaces, you can do a search and replace
> > > on column AX: search for two spaces and replace with 1 space. Repeat until
> > > Excel tell you there are no more matches.
> > >
> > > On Mon, 7 Mar 2005 20:17:02 -0800, George A. Yorks
> > > <[email protected].(donotspam)> wrote:
> > >
> > > >In using the lookup formula I find that the data in column AX3 is not exactly
> > > >like that in sheet 2 B4. ie: the spacing between first and last name is not
> > > >the same. Any way to correct this. I only know of the differences when the
> > > >dollar amounts are not tranfered. I then have to correct these errors
> > > >manually. Hope this makes sense
> > > >
> > > >Thanks for all the help
> > > >
> > > >"George A. Yorks" wrote:
> > > >
> > > >> I tried this formula and get a "not found" also this is how the formula
> > > gets
> > > >> entered. Don't know why. It was entered as below.
> > > >> =IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
> > > >>
> > > found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)))
> > > >> "RagDyeR" wrote:
> > > >>
> > > >> > Try this in Ay3 of "Miriam":
> > > >> >
> > > >> > =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
> > > >> > Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$18,0)))
> > > >> >
> > > >> > You can then *double click* on the "fill handle" in the lower right
> > > corner
> > > >> > of AY3, which will *automatically* copy the formula in AY3 down Column
> > > AY,
> > > >> > as far as there is data in Column AX.
> > > >> > --
> > > >> >
> > > >> > HTH,
> > > >> >
> > > >> > RD
> > > >> > ==============================================
> > > >> > Please keep all correspondence within the Group, so all may benefit!
> > > >> > ==============================================
> > > >> >
> > > >> >
> > > >> > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > >> > news:[email protected]...
> > > >> > Thanks for the help. I am confronted with a new scenario which I'll
> > > present
> > > >> > for help.
> > > >> >
> > > >> > One worksheet titled geo I have a list of names b4 thru b18 and data in
> > > >> > k4thru k18. I want to transfere the data to worksheet titled miriam
> > > after
> > > >> > searching for the exact names. The names of the second worksheet are
> > > column
> > > >> > ax3 thru ax89. Hope this makes sense to you.
> > > >> >
> > > >> > George
> > > >> >
> > > >> > "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)),"",VLOOKUP(A1,
> > > >> > > 'Sheet2'!$A$1:$D$10, 4, 0)
> > > >> > > --
> > > >> > >
> > > >> > > HTH,
> > > >> > >
> > > >> > > RD
> > > >> > > ==============================================
> > > >> > > Please keep all correspondence within the Group, so all may benefit!
> > > >> > > ==============================================
> > > >> > >
> > > >> > >
> > > >> > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > >> > > news:[email protected]...
> > > >> > > 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" <[email protected].(donotspam)> wrote in message
> > > >> > > > news:[email protected]...
> > > >> > > > > 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" <[email protected].(donotspam)> wrote in
> > > message
> > > >> > > > >> news:[email protected]...
> > > >> > > > >> > 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" <[email protected].(donotspam)> wrote in
> > > >> > message
> > > >> > > > >> > > news:[email protected]...
> > > >> > > > >> > > > 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
> > > >> > > > >> > >
> > > >> > > > >> > >
> > > >> > > > >> > >
> > > >> > > > >>
> > > >> > > > >>
> > > >> > > >
> > > >> > > >
> > > >> > > >
> > > >> > >
> > > >> > >
> > > >> >
> > > >> >
> > > >> >
> > >
> > >
You're still trying to solve this "old" problem?
To start from scratch:
Case 1:
For "regular" spaces, follow Myrna's suggestion:
Select the column of names, then,
<Edit> <Replace>,
In the "Find What" box, hit the <Space> bar 2 times,
In the "Replace With" box, hit the <Space> bar 1 time.
Then, "Replace All".
*Repeat* this a couple of times, where you enter 3 and then 4 spaces in the
"Find What" box, always replacing with a single space.
See if this helps the situation to *any* extant.
If some do match, but not all, you might have to repeat, using more and more
spaces in the "Find What" box.
Case 2:
If there's no improvement replacing "regular" spaces (Char(32)), try
replacing "non-breaking" (Char(160)) spaces.
Naturally, you *can't* use the <Space> bar for these.
Make sure the "Find What" box is empty.
It tends to "remember" the criteria from your last search, and of course,
you can't see those spaces you entered.
Now, enter the "non-breaking" space in the "Find What" box by:
Hold <Alt>,
And type the number,
0160
Using the Num keypad, *not* the numbers under the function keys.
And of course, you will not see anything in the "Find What" box, since those
keystrokes DO produce a space.
Again, enter your single "regular" space in the "Replace" box.
If this doesn't help, and you're still having a problem after all this, you
could try to identify what character is exactly between the names.
Try this formula:
=CODE(MID(A1,5,1))
Where A1 is the cell containing one of the "problem" names, and the "5" is
the character count of the first space in the name, counting from the left.
If you had a problem name of George Washington in cell G5, you would revise
the formula to:
=CODE(MID(G5,7,1))
This should return a "32" for a normal space,
Or a "160" for a non-breaking space.
If it looks like there is more then a single space, just increment the
number in the formula to test those "other" spaces.
AND, if you do get another number returned, just use that number in the
"Edit & Replace" procedure, making sure that you do use *4* digits, with
leading zeroes where necessary.
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
"George A. Yorks" <[email protected].(donotspam)> wrote in message
news:[email protected]...
> In using find and replace, I select the full column do ctrl. h to get find
> and replace but still trying to figure how to look for three spaces in
find
> and two spaces in replace. If I use * * that is all that gets replaced
> --
> George
>
>
> "George A. Yorks" wrote:
>
> > I am still not able to adjust space between two words. I have a column
of
> > names with two or three spaces between first and last name. I want to
adjust
> > so there is only one space between the two names. How to do this. Was
> > suggested I do a search and replace but have not found how to do this.
Any
> > help appreciated
> > --
> > George
> >
> >
> > "George A. Yorks" wrote:
> >
> > > Have looked through help menu for how to perform a search and replace
you
> > > speak of. I can't find anything. Could you give me some direction.
Thanks
> > > for all your help
> > >
> > > "Myrna Larson" wrote:
> > >
> > > > If you sometimes have double or triple spaces, you can do a search
and replace
> > > > on column AX: search for two spaces and replace with 1 space. Repeat
until
> > > > Excel tell you there are no more matches.
> > > >
> > > > On Mon, 7 Mar 2005 20:17:02 -0800, George A. Yorks
> > > > <[email protected].(donotspam)> wrote:
> > > >
> > > > >In using the lookup formula I find that the data in column AX3 is
not exactly
> > > > >like that in sheet 2 B4. ie: the spacing between first and last
name is not
> > > > >the same. Any way to correct this. I only know of the differences
when the
> > > > >dollar amounts are not tranfered. I then have to correct these
errors
> > > > >manually. Hope this makes sense
> > > > >
> > > > >Thanks for all the help
> > > > >
> > > > >"George A. Yorks" wrote:
> > > > >
> > > > >> I tried this formula and get a "not found" also this is how the
formula
> > > > gets
> > > > >> entered. Don't know why. It was entered as below.
> > > > >> =IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
> > > > >>
> > > >
found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18
,0)))
> > > > >> "RagDyeR" wrote:
> > > > >>
> > > > >> > Try this in Ay3 of "Miriam":
> > > > >> >
> > > > >> > =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
> > > > >> > Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$18,0)))
> > > > >> >
> > > > >> > You can then *double click* on the "fill handle" in the lower
right
> > > > corner
> > > > >> > of AY3, which will *automatically* copy the formula in AY3 down
Column
> > > > AY,
> > > > >> > as far as there is data in Column AX.
> > > > >> > --
> > > > >> >
> > > > >> > HTH,
> > > > >> >
> > > > >> > RD
> > > > >> > ==============================================
> > > > >> > Please keep all correspondence within the Group, so all may
benefit!
> > > > >> > ==============================================
> > > > >> >
> > > > >> >
> > > > >> > "George A. Yorks" <[email protected].(donotspam)> wrote in
message
> > > > >> > news:[email protected]...
> > > > >> > Thanks for the help. I am confronted with a new scenario which
I'll
> > > > present
> > > > >> > for help.
> > > > >> >
> > > > >> > One worksheet titled geo I have a list of names b4 thru b18
and data in
> > > > >> > k4thru k18. I want to transfere the data to worksheet titled
miriam
> > > > after
> > > > >> > searching for the exact names. The names of the second
worksheet are
> > > > column
> > > > >> > ax3 thru ax89. Hope this makes sense to you.
> > > > >> >
> > > > >> > George
> > > > >> >
> > > > >> > "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)),"",VLOOKUP(A1,
> > > > >> > > 'Sheet2'!$A$1:$D$10, 4, 0)
> > > > >> > > --
> > > > >> > >
> > > > >> > > HTH,
> > > > >> > >
> > > > >> > > RD
> > > > >> > > ==============================================
> > > > >> > > Please keep all correspondence within the Group, so all may
benefit!
> > > > >> > > ==============================================
> > > > >> > >
> > > > >> > >
> > > > >> > > "George A. Yorks" <[email protected].(donotspam)> wrote in
message
> > > > >> > > news:[email protected]...
> > > > >> > > 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" <[email protected].(donotspam)> wrote in
message
> > > > >> > > > news:[email protected]...
> > > > >> > > > > 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" <[email protected].(donotspam)> wrote
in
> > > > message
> > > > >> > > > >>
news:[email protected]...
> > > > >> > > > >> > 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" <[email protected].(donotspam)>
wrote in
> > > > >> > message
> > > > >> > > > >> > >
news:[email protected]...
> > > > >> > > > >> > > > 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
> > > > >> > > > >> > >
> > > > >> > > > >> > >
> > > > >> > > > >> > >
> > > > >> > > > >>
> > > > >> > > > >>
> > > > >> > > >
> > > > >> > > >
> > > > >> > > >
> > > > >> > >
> > > > >> > >
> > > > >> >
> > > > >> >
> > > > >> >
> > > >
> > > >
I have tried to use the suggested formula (=code(mid(a1,5,1,)) and in case
one received 32 as result. when I then go to search replace I have entered
spaces to search and spaces to replace( four blank spaces to search and two
blank spaces to replace.)This does not work and can't figure how else to use
the search replace. Any further help appreciated
--
George
"RagDyer" wrote:
> I would guess that perhaps the data that's in AX3 doesn't *exactly* match
> what's in Column B.
>
> Do you import any of your data?
> Are the names "full" names, first, and/or middle and last names, where there
> might be a possibilty that the spaces between them might not be a normal
> Char(32) space?
> Could there be a possibility of leading and/or trailing spaces?
>
> For a test, key a name into Column B.
> Enter that same name in *exactly* the same way into AX3, and see if you get
> a correct return from your formula.
> --
>
>
> Regards,
>
> RD
> --------------------------------------------------------------------
> Please keep all correspondence within the Group, so all may benefit!
> -------------------------------------------------------------------
>
> "George A. Yorks" <[email protected].(donotspam)> wrote in message
> news:[email protected]...
> I tried this formula and get a "not found" also this is how the formula gets
> entered. Don't know why. It was entered as below.
> =IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
> found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18
> ,0)))
> "RagDyeR" wrote:
>
> > Try this in Ay3 of "Miriam":
> >
> > =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
> > Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$18,0)))
> >
> > You can then *double click* on the "fill handle" in the lower right corner
> > of AY3, which will *automatically* copy the formula in AY3 down Column AY,
> > as far as there is data in Column AX.
> > --
> >
> > HTH,
> >
> > RD
> > ==============================================
> > Please keep all correspondence within the Group, so all may benefit!
> > ==============================================
> >
> >
> > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > news:[email protected]...
> > Thanks for the help. I am confronted with a new scenario which I'll
> present
> > for help.
> >
> > One worksheet titled geo I have a list of names b4 thru b18 and data in
> > k4thru k18. I want to transfere the data to worksheet titled miriam after
> > searching for the exact names. The names of the second worksheet are
> column
> > ax3 thru ax89. Hope this makes sense to you.
> >
> > George
> >
> > "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)),"",VLOOKUP(A1,
> > > 'Sheet2'!$A$1:$D$10, 4, 0)
> > > --
> > >
> > > HTH,
> > >
> > > RD
> > > ==============================================
> > > Please keep all correspondence within the Group, so all may benefit!
> > > ==============================================
> > >
> > >
> > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > news:[email protected]...
> > > 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" <[email protected].(donotspam)> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected].(donotspam)> wrote in message
> > > > >> news:[email protected]...
> > > > >> > 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" <[email protected].(donotspam)> wrote in
> > message
> > > > >> > > news:[email protected]...
> > > > >> > > > 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
> > > > >> > >
> > > > >> > >
> > > > >> > >
> > > > >>
> > > > >>
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >
>
>
If you wish, you can send me your sheet, and I'll see what I can figure out.
Cut out cutout from my address.
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"George A. Yorks" <[email protected].(donotspam)> wrote in message
news:[email protected]...
> I have tried to use the suggested formula (=code(mid(a1,5,1,)) and in case
> one received 32 as result. when I then go to search replace I have
entered
> spaces to search and spaces to replace( four blank spaces to search and
two
> blank spaces to replace.)This does not work and can't figure how else to
use
> the search replace. Any further help appreciated
> --
> George
>
>
> "RagDyer" wrote:
>
> > I would guess that perhaps the data that's in AX3 doesn't *exactly*
match
> > what's in Column B.
> >
> > Do you import any of your data?
> > Are the names "full" names, first, and/or middle and last names, where
there
> > might be a possibilty that the spaces between them might not be a normal
> > Char(32) space?
> > Could there be a possibility of leading and/or trailing spaces?
> >
> > For a test, key a name into Column B.
> > Enter that same name in *exactly* the same way into AX3, and see if you
get
> > a correct return from your formula.
> > --
> >
> >
> > Regards,
> >
> > RD
> > --------------------------------------------------------------------
> > Please keep all correspondence within the Group, so all may benefit!
> > -------------------------------------------------------------------
> >
> > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > news:[email protected]...
> > I tried this formula and get a "not found" also this is how the formula
gets
> > entered. Don't know why. It was entered as below.
> > =IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
> >
found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18
> > ,0)))
> > "RagDyeR" wrote:
> >
> > > Try this in Ay3 of "Miriam":
> > >
> > > =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
> > > Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$18,0)))
> > >
> > > You can then *double click* on the "fill handle" in the lower right
corner
> > > of AY3, which will *automatically* copy the formula in AY3 down Column
AY,
> > > as far as there is data in Column AX.
> > > --
> > >
> > > HTH,
> > >
> > > RD
> > > ==============================================
> > > Please keep all correspondence within the Group, so all may benefit!
> > > ==============================================
> > >
> > >
> > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > news:[email protected]...
> > > Thanks for the help. I am confronted with a new scenario which I'll
> > present
> > > for help.
> > >
> > > One worksheet titled geo I have a list of names b4 thru b18 and data
in
> > > k4thru k18. I want to transfere the data to worksheet titled miriam
after
> > > searching for the exact names. The names of the second worksheet are
> > column
> > > ax3 thru ax89. Hope this makes sense to you.
> > >
> > > George
> > >
> > > "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)),"",VLOOKUP(A1,
> > > > 'Sheet2'!$A$1:$D$10, 4, 0)
> > > > --
> > > >
> > > > HTH,
> > > >
> > > > RD
> > > > ==============================================
> > > > Please keep all correspondence within the Group, so all may benefit!
> > > > ==============================================
> > > >
> > > >
> > > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > > news:[email protected]...
> > > > 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" <[email protected].(donotspam)> wrote in
message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected].(donotspam)> wrote in
message
> > > > > >> news:[email protected]...
> > > > > >> > 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" <[email protected].(donotspam)> wrote in
> > > message
> > > > > >> > > news:[email protected]...
> > > > > >> > > > 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
> > > > > >> > >
> > > > > >> > >
> > > > > >> > >
> > > > > >>
> > > > > >>
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
Sean O'Hair   $957,225
Rod Pampling $931,189
Geoff Ogilvy $928,444
Billy Andrade $915,285
Jonathan Kaye $858,395
Lucas Glover   $822,434
Aaron Baddeley $805,982
Joe Durant $790,169
Shigeki Maruyama $786,922
John Daly 1 $778,132
Scott McCarron $764,649
James Driscoll   $757,239
Bob Tway $714,841
Brian Davis   $711,804
The list above shows names with the A A as an extention. This seems to
create my problem. If I try to simply delete these "A A" the names can not
be properly spaced. This probably results in the hidden spaces you speak of.
Is there any method to remove the extension and allow for proper spacing.
After all your help it appears to boil down to this.
--
George
"Ragdyer" wrote:
> If you wish, you can send me your sheet, and I'll see what I can figure out.
>
> Cut out cutout from my address.
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "George A. Yorks" <[email protected].(donotspam)> wrote in message
> news:[email protected]...
> > I have tried to use the suggested formula (=code(mid(a1,5,1,)) and in case
> > one received 32 as result. when I then go to search replace I have
> entered
> > spaces to search and spaces to replace( four blank spaces to search and
> two
> > blank spaces to replace.)This does not work and can't figure how else to
> use
> > the search replace. Any further help appreciated
> > --
> > George
> >
> >
> > "RagDyer" wrote:
> >
> > > I would guess that perhaps the data that's in AX3 doesn't *exactly*
> match
> > > what's in Column B.
> > >
> > > Do you import any of your data?
> > > Are the names "full" names, first, and/or middle and last names, where
> there
> > > might be a possibilty that the spaces between them might not be a normal
> > > Char(32) space?
> > > Could there be a possibility of leading and/or trailing spaces?
> > >
> > > For a test, key a name into Column B.
> > > Enter that same name in *exactly* the same way into AX3, and see if you
> get
> > > a correct return from your formula.
> > > --
> > >
> > >
> > > Regards,
> > >
> > > RD
> > > --------------------------------------------------------------------
> > > Please keep all correspondence within the Group, so all may benefit!
> > > -------------------------------------------------------------------
> > >
> > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > news:[email protected]...
> > > I tried this formula and get a "not found" also this is how the formula
> gets
> > > entered. Don't know why. It was entered as below.
> > > =IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
> > >
> found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18
> > > ,0)))
> > > "RagDyeR" wrote:
> > >
> > > > Try this in Ay3 of "Miriam":
> > > >
> > > > =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
> > > > Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$18,0)))
> > > >
> > > > You can then *double click* on the "fill handle" in the lower right
> corner
> > > > of AY3, which will *automatically* copy the formula in AY3 down Column
> AY,
> > > > as far as there is data in Column AX.
> > > > --
> > > >
> > > > HTH,
> > > >
> > > > RD
> > > > ==============================================
> > > > Please keep all correspondence within the Group, so all may benefit!
> > > > ==============================================
> > > >
> > > >
> > > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > > news:[email protected]...
> > > > Thanks for the help. I am confronted with a new scenario which I'll
> > > present
> > > > for help.
> > > >
> > > > One worksheet titled geo I have a list of names b4 thru b18 and data
> in
> > > > k4thru k18. I want to transfere the data to worksheet titled miriam
> after
> > > > searching for the exact names. The names of the second worksheet are
> > > column
> > > > ax3 thru ax89. Hope this makes sense to you.
> > > >
> > > > George
> > > >
> > > > "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)),"",VLOOKUP(A1,
> > > > > 'Sheet2'!$A$1:$D$10, 4, 0)
> > > > > --
> > > > >
> > > > > HTH,
> > > > >
> > > > > RD
> > > > > ==============================================
> > > > > Please keep all correspondence within the Group, so all may benefit!
> > > > > ==============================================
> > > > >
> > > > >
> > > > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > > > news:[email protected]...
> > > > > 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" <[email protected].(donotspam)> wrote in
> message
> > > > > > news:[email protected]...
> > > > > > > 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" <[email protected].(donotspam)> wrote in
> message
> > > > > > >> news:[email protected]...
> > > > > > >> > 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" <[email protected].(donotspam)> wrote in
> > > > message
> > > > > > >> > > news:[email protected]...
> > > > > > >> > > > 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
> > > > > > >> > >
> > > > > > >> > >
> > > > > > >> > >
> > > > > > >>
> > > > > > >>
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > >
> > >
>
>
I can't tell what your data contains from just looking at it in your post.
If you don't wish to send me a copy, there's really nothing more I can
suggest to you, besides perhaps the outside chance that "Text To Columns"
might accomplish something.
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------
"George A. Yorks" <[email protected].(donotspam)> wrote in message
news:[email protected]...
> Sean O'Hair   $957,225
> Rod Pampling $931,189
> Geoff Ogilvy $928,444
> Billy Andrade $915,285
> Jonathan Kaye $858,395
> Lucas Glover   $822,434
> Aaron Baddeley $805,982
> Joe Durant $790,169
> Shigeki Maruyama $786,922
> John Daly 1 $778,132
> Scott McCarron $764,649
> James Driscoll   $757,239
> Bob Tway $714,841
> Brian Davis   $711,804
> The list above shows names with the A A as an extention. This seems to
> create my problem. If I try to simply delete these "A A" the names can
not
> be properly spaced. This probably results in the hidden spaces you speak
of.
> Is there any method to remove the extension and allow for proper spacing.
> After all your help it appears to boil down to this.
> --
> George
>
>
> "Ragdyer" wrote:
>
> > If you wish, you can send me your sheet, and I'll see what I can figure
out.
> >
> > Cut out cutout from my address.
> > --
> > Regards,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
-
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
-
> > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > news:[email protected]...
> > > I have tried to use the suggested formula (=code(mid(a1,5,1,)) and in
case
> > > one received 32 as result. when I then go to search replace I have
> > entered
> > > spaces to search and spaces to replace( four blank spaces to search
and
> > two
> > > blank spaces to replace.)This does not work and can't figure how else
to
> > use
> > > the search replace. Any further help appreciated
> > > --
> > > George
> > >
> > >
> > > "RagDyer" wrote:
> > >
> > > > I would guess that perhaps the data that's in AX3 doesn't *exactly*
> > match
> > > > what's in Column B.
> > > >
> > > > Do you import any of your data?
> > > > Are the names "full" names, first, and/or middle and last names,
where
> > there
> > > > might be a possibilty that the spaces between them might not be a
normal
> > > > Char(32) space?
> > > > Could there be a possibility of leading and/or trailing spaces?
> > > >
> > > > For a test, key a name into Column B.
> > > > Enter that same name in *exactly* the same way into AX3, and see if
you
> > get
> > > > a correct return from your formula.
> > > > --
> > > >
> > > >
> > > > Regards,
> > > >
> > > > RD
> > > > --------------------------------------------------------------------
> > > > Please keep all correspondence within the Group, so all may benefit!
> > > > -------------------------------------------------------------------
> > > >
> > > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > > news:[email protected]...
> > > > I tried this formula and get a "not found" also this is how the
formula
> > gets
> > > > entered. Don't know why. It was entered as below.
> > > > =IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
> > > >
> >
found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18
> > > > ,0)))
> > > > "RagDyeR" wrote:
> > > >
> > > > > Try this in Ay3 of "Miriam":
> > > > >
> > > > > =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
> > > > > Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$18,0)))
> > > > >
> > > > > You can then *double click* on the "fill handle" in the lower
right
> > corner
> > > > > of AY3, which will *automatically* copy the formula in AY3 down
Column
> > AY,
> > > > > as far as there is data in Column AX.
> > > > > --
> > > > >
> > > > > HTH,
> > > > >
> > > > > RD
> > > > > ==============================================
> > > > > Please keep all correspondence within the Group, so all may
benefit!
> > > > > ==============================================
> > > > >
> > > > >
> > > > > "George A. Yorks" <[email protected].(donotspam)> wrote in
message
> > > > > news:[email protected]...
> > > > > Thanks for the help. I am confronted with a new scenario which
I'll
> > > > present
> > > > > for help.
> > > > >
> > > > > One worksheet titled geo I have a list of names b4 thru b18 and
data
> > in
> > > > > k4thru k18. I want to transfere the data to worksheet titled
miriam
> > after
> > > > > searching for the exact names. The names of the second worksheet
are
> > > > column
> > > > > ax3 thru ax89. Hope this makes sense to you.
> > > > >
> > > > > George
> > > > >
> > > > > "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)),"",VLOOKUP(A1,
> > > > > > 'Sheet2'!$A$1:$D$10, 4, 0)
> > > > > > --
> > > > > >
> > > > > > HTH,
> > > > > >
> > > > > > RD
> > > > > > ==============================================
> > > > > > Please keep all correspondence within the Group, so all may
benefit!
> > > > > > ==============================================
> > > > > >
> > > > > >
> > > > > > "George A. Yorks" <[email protected].(donotspam)> wrote in
message
> > > > > > news:[email protected]...
> > > > > > 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" <[email protected].(donotspam)> wrote in
> > message
> > > > > > > news:[email protected]...
> > > > > > > > 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" <[email protected].(donotspam)> wrote in
> > message
> > > > > > > >> news:[email protected]...
> > > > > > > >> > 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" <[email protected].(donotspam)>
wrote in
> > > > > message
> > > > > > > >> > >
news:[email protected]...
> > > > > > > >> > > > 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
> > > > > > > >> > >
> > > > > > > >> > >
> > > > > > > >> > >
> > > > > > > >>
> > > > > > > >>
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> >
> >
I thought what I sent would help one last thought. I've tried to use the
trim and/or clean funtion to remove what appears to be a hidden character and
hidden space. The character is removed the spaces of the name is correct but
when I reverse the names (from first and last) to (last and first)using a
workable formula the spacing reverts tothe incorrect multiple spacing. It's
mind boggling
--
George
"RagDyer" wrote:
> I can't tell what your data contains from just looking at it in your post.
>
> If you don't wish to send me a copy, there's really nothing more I can
> suggest to you, besides perhaps the outside chance that "Text To Columns"
> might accomplish something.
> --
> Regards,
>
> RD
> --------------------------------------------------------------------
> Please keep all correspondence within the Group, so all may benefit!
> -------------------------------------------------------------------
>
> "George A. Yorks" <[email protected].(donotspam)> wrote in message
> news:[email protected]...
> > Sean O'Hair   $957,225
> > Rod Pampling $931,189
> > Geoff Ogilvy $928,444
> > Billy Andrade $915,285
> > Jonathan Kaye $858,395
> > Lucas Glover   $822,434
> > Aaron Baddeley $805,982
> > Joe Durant $790,169
> > Shigeki Maruyama $786,922
> > John Daly 1 $778,132
> > Scott McCarron $764,649
> > James Driscoll   $757,239
> > Bob Tway $714,841
> > Brian Davis   $711,804
> > The list above shows names with the A A as an extention. This seems to
> > create my problem. If I try to simply delete these "A A" the names can
> not
> > be properly spaced. This probably results in the hidden spaces you speak
> of.
> > Is there any method to remove the extension and allow for proper spacing.
> > After all your help it appears to boil down to this.
> > --
> > George
> >
> >
> > "Ragdyer" wrote:
> >
> > > If you wish, you can send me your sheet, and I'll see what I can figure
> out.
> > >
> > > Cut out cutout from my address.
> > > --
> > > Regards,
> > >
> > > RD
> > >
> >
> > --------------------------------------------------------------------------
> -
> > > Please keep all correspondence within the NewsGroup, so all may benefit
> !
> >
> > --------------------------------------------------------------------------
> -
> > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > news:[email protected]...
> > > > I have tried to use the suggested formula (=code(mid(a1,5,1,)) and in
> case
> > > > one received 32 as result. when I then go to search replace I have
> > > entered
> > > > spaces to search and spaces to replace( four blank spaces to search
> and
> > > two
> > > > blank spaces to replace.)This does not work and can't figure how else
> to
> > > use
> > > > the search replace. Any further help appreciated
> > > > --
> > > > George
> > > >
> > > >
> > > > "RagDyer" wrote:
> > > >
> > > > > I would guess that perhaps the data that's in AX3 doesn't *exactly*
> > > match
> > > > > what's in Column B.
> > > > >
> > > > > Do you import any of your data?
> > > > > Are the names "full" names, first, and/or middle and last names,
> where
> > > there
> > > > > might be a possibilty that the spaces between them might not be a
> normal
> > > > > Char(32) space?
> > > > > Could there be a possibility of leading and/or trailing spaces?
> > > > >
> > > > > For a test, key a name into Column B.
> > > > > Enter that same name in *exactly* the same way into AX3, and see if
> you
> > > get
> > > > > a correct return from your formula.
> > > > > --
> > > > >
> > > > >
> > > > > Regards,
> > > > >
> > > > > RD
> > > > > --------------------------------------------------------------------
> > > > > Please keep all correspondence within the Group, so all may benefit!
> > > > > -------------------------------------------------------------------
> > > > >
> > > > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > > > news:[email protected]...
> > > > > I tried this formula and get a "not found" also this is how the
> formula
> > > gets
> > > > > entered. Don't know why. It was entered as below.
> > > > > =IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
> > > > >
> > >
> found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18
> > > > > ,0)))
> > > > > "RagDyeR" wrote:
> > > > >
> > > > > > Try this in Ay3 of "Miriam":
> > > > > >
> > > > > > =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
> > > > > > Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$18,0)))
> > > > > >
> > > > > > You can then *double click* on the "fill handle" in the lower
> right
> > > corner
> > > > > > of AY3, which will *automatically* copy the formula in AY3 down
> Column
> > > AY,
> > > > > > as far as there is data in Column AX.
> > > > > > --
> > > > > >
> > > > > > HTH,
> > > > > >
> > > > > > RD
> > > > > > ==============================================
> > > > > > Please keep all correspondence within the Group, so all may
> benefit!
> > > > > > ==============================================
> > > > > >
> > > > > >
> > > > > > "George A. Yorks" <[email protected].(donotspam)> wrote in
> message
> > > > > > news:[email protected]...
> > > > > > Thanks for the help. I am confronted with a new scenario which
> I'll
> > > > > present
> > > > > > for help.
> > > > > >
> > > > > > One worksheet titled geo I have a list of names b4 thru b18 and
> data
> > > in
> > > > > > k4thru k18. I want to transfere the data to worksheet titled
> miriam
> > > after
> > > > > > searching for the exact names. The names of the second worksheet
> are
> > > > > column
> > > > > > ax3 thru ax89. Hope this makes sense to you.
> > > > > >
> > > > > > George
> > > > > >
> > > > > > "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)),"",VLOOKUP(A1,
> > > > > > > 'Sheet2'!$A$1:$D$10, 4, 0)
> > > > > > > --
> > > > > > >
> > > > > > > HTH,
> > > > > > >
> > > > > > > RD
> > > > > > > ==============================================
> > > > > > > Please keep all correspondence within the Group, so all may
> benefit!
> > > > > > > ==============================================
> > > > > > >
> > > > > > >
> > > > > > > "George A. Yorks" <[email protected].(donotspam)> wrote in
> message
> > > > > > > news:[email protected]...
> > > > > > > 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" <[email protected].(donotspam)> wrote in
> > > message
> > > > > > > > news:[email protected]...
> > > > > > > > > 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" <[email protected].(donotspam)> wrote in
> > > message
> > > > > > > > >> news:[email protected]...
> > > > > > > > >> > 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
I would like to send the sheet to you however all I have is a list of names
and dollar amounts. This represent earnings that I am tracking. There is
the additional letter as a trailer to some of the names.(this designates
participation in an additional group. The use of clean function does
deletethe letter but the hidden space remains. The use of the trim function
can not correct this situation. I copy the list from the web. If you can
suggest how I might send it to you I will be pleasedto do so. Or any other
suggestions you might have
--
George
"Ragdyer" wrote:
> If you wish, you can send me your sheet, and I'll see what I can figure out.
>
> Cut out cutout from my address.
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "George A. Yorks" <[email protected].(donotspam)> wrote in message
> news:[email protected]...
> > I have tried to use the suggested formula (=code(mid(a1,5,1,)) and in case
> > one received 32 as result. when I then go to search replace I have
> entered
> > spaces to search and spaces to replace( four blank spaces to search and
> two
> > blank spaces to replace.)This does not work and can't figure how else to
> use
> > the search replace. Any further help appreciated
> > --
> > George
> >
> >
> > "RagDyer" wrote:
> >
> > > I would guess that perhaps the data that's in AX3 doesn't *exactly*
> match
> > > what's in Column B.
> > >
> > > Do you import any of your data?
> > > Are the names "full" names, first, and/or middle and last names, where
> there
> > > might be a possibilty that the spaces between them might not be a normal
> > > Char(32) space?
> > > Could there be a possibility of leading and/or trailing spaces?
> > >
> > > For a test, key a name into Column B.
> > > Enter that same name in *exactly* the same way into AX3, and see if you
> get
> > > a correct return from your formula.
> > > --
> > >
> > >
> > > Regards,
> > >
> > > RD
> > > --------------------------------------------------------------------
> > > Please keep all correspondence within the Group, so all may benefit!
> > > -------------------------------------------------------------------
> > >
> > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > news:[email protected]...
> > > I tried this formula and get a "not found" also this is how the formula
> gets
> > > entered. Don't know why. It was entered as below.
> > > =IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
> > >
> found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18
> > > ,0)))
> > > "RagDyeR" wrote:
> > >
> > > > Try this in Ay3 of "Miriam":
> > > >
> > > > =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
> > > > Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$18,0)))
> > > >
> > > > You can then *double click* on the "fill handle" in the lower right
> corner
> > > > of AY3, which will *automatically* copy the formula in AY3 down Column
> AY,
> > > > as far as there is data in Column AX.
> > > > --
> > > >
> > > > HTH,
> > > >
> > > > RD
> > > > ==============================================
> > > > Please keep all correspondence within the Group, so all may benefit!
> > > > ==============================================
> > > >
> > > >
> > > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > > news:[email protected]...
> > > > Thanks for the help. I am confronted with a new scenario which I'll
> > > present
> > > > for help.
> > > >
> > > > One worksheet titled geo I have a list of names b4 thru b18 and data
> in
> > > > k4thru k18. I want to transfere the data to worksheet titled miriam
> after
> > > > searching for the exact names. The names of the second worksheet are
> > > column
> > > > ax3 thru ax89. Hope this makes sense to you.
> > > >
> > > > George
> > > >
> > > > "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)),"",VLOOKUP(A1,
> > > > > 'Sheet2'!$A$1:$D$10, 4, 0)
> > > > > --
> > > > >
> > > > > HTH,
> > > > >
> > > > > RD
> > > > > ==============================================
> > > > > Please keep all correspondence within the Group, so all may benefit!
> > > > > ==============================================
> > > > >
> > > > >
> > > > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > > > news:[email protected]...
> > > > > 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" <[email protected].(donotspam)> wrote in
> message
> > > > > > news:[email protected]...
> > > > > > > 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" <[email protected].(donotspam)> wrote in
> message
> > > > > > >> news:[email protected]...
> > > > > > >> > 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" <[email protected].(donotspam)> wrote in
> > > > message
> > > > > > >> > > news:[email protected]...
> > > > > > >> > > > 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
> > > > > > >> > >
> > > > > > >> > >
> > > > > > >> > >
> > > > > > >>
> > > > > > >>
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > >
> > >
>
>
A new question.
I am able to copy data from column of numbers to a corresponding column
using the following basic formula =b11 to represent column b row 11. In the
corresponding column I can drag the first cell data down the column and all
data is transfered. I there a formula to use to data from rows to columns
using the same concept. I am looking to have the column heading change and
the data row remaing the same.
--
George
"George A. Yorks" wrote:
> I would like to send the sheet to you however all I have is a list of names
> and dollar amounts. This represent earnings that I am tracking. There is
> the additional letter as a trailer to some of the names.(this designates
> participation in an additional group. The use of clean function does
> deletethe letter but the hidden space remains. The use of the trim function
> can not correct this situation. I copy the list from the web. If you can
> suggest how I might send it to you I will be pleasedto do so. Or any other
> suggestions you might have
> --
> George
>
>
> "Ragdyer" wrote:
>
> > If you wish, you can send me your sheet, and I'll see what I can figure out.
> >
> > Cut out cutout from my address.
> > --
> > Regards,
> >
> > RD
> >
> > ---------------------------------------------------------------------------
> > Please keep all correspondence within the NewsGroup, so all may benefit !
> > ---------------------------------------------------------------------------
> > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > news:[email protected]...
> > > I have tried to use the suggested formula (=code(mid(a1,5,1,)) and in case
> > > one received 32 as result. when I then go to search replace I have
> > entered
> > > spaces to search and spaces to replace( four blank spaces to search and
> > two
> > > blank spaces to replace.)This does not work and can't figure how else to
> > use
> > > the search replace. Any further help appreciated
> > > --
> > > George
> > >
> > >
> > > "RagDyer" wrote:
> > >
> > > > I would guess that perhaps the data that's in AX3 doesn't *exactly*
> > match
> > > > what's in Column B.
> > > >
> > > > Do you import any of your data?
> > > > Are the names "full" names, first, and/or middle and last names, where
> > there
> > > > might be a possibilty that the spaces between them might not be a normal
> > > > Char(32) space?
> > > > Could there be a possibility of leading and/or trailing spaces?
> > > >
> > > > For a test, key a name into Column B.
> > > > Enter that same name in *exactly* the same way into AX3, and see if you
> > get
> > > > a correct return from your formula.
> > > > --
> > > >
> > > >
> > > > Regards,
> > > >
> > > > RD
> > > > --------------------------------------------------------------------
> > > > Please keep all correspondence within the Group, so all may benefit!
> > > > -------------------------------------------------------------------
> > > >
> > > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > > news:[email protected]...
> > > > I tried this formula and get a "not found" also this is how the formula
> > gets
> > > > entered. Don't know why. It was entered as below.
> > > > =IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
> > > >
> > found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18
> > > > ,0)))
> > > > "RagDyeR" wrote:
> > > >
> > > > > Try this in Ay3 of "Miriam":
> > > > >
> > > > > =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
> > > > > Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$18,0)))
> > > > >
> > > > > You can then *double click* on the "fill handle" in the lower right
> > corner
> > > > > of AY3, which will *automatically* copy the formula in AY3 down Column
> > AY,
> > > > > as far as there is data in Column AX.
> > > > > --
> > > > >
> > > > > HTH,
> > > > >
> > > > > RD
> > > > > ==============================================
> > > > > Please keep all correspondence within the Group, so all may benefit!
> > > > > ==============================================
> > > > >
> > > > >
> > > > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > > > news:[email protected]...
> > > > > Thanks for the help. I am confronted with a new scenario which I'll
> > > > present
> > > > > for help.
> > > > >
> > > > > One worksheet titled geo I have a list of names b4 thru b18 and data
> > in
> > > > > k4thru k18. I want to transfere the data to worksheet titled miriam
> > after
> > > > > searching for the exact names. The names of the second worksheet are
> > > > column
> > > > > ax3 thru ax89. Hope this makes sense to you.
> > > > >
> > > > > George
> > > > >
> > > > > "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)),"",VLOOKUP(A1,
> > > > > > 'Sheet2'!$A$1:$D$10, 4, 0)
> > > > > > --
> > > > > >
> > > > > > HTH,
> > > > > >
> > > > > > RD
> > > > > > ==============================================
> > > > > > Please keep all correspondence within the Group, so all may benefit!
> > > > > > ==============================================
> > > > > >
> > > > > >
> > > > > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > > > > news:[email protected]...
> > > > > > 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" <[email protected].(donotspam)> wrote in
> > message
> > > > > > > news:[email protected]...
> > > > > > > > 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" <[email protected].(donotspam)> wrote in
> > message
> > > > > > > >> news:[email protected]...
> > > > > > > >> > 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" <[email protected].(donotspam)> wrote in
> > > > > message
> > > > > > > >> > > news:[email protected]...
> > > > > > > >> > > > 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
> > > > > > > >> > >
> > > > > > > >> > >
> > > > > > > >> > >
> > > > > > > >>
> > > > > > > >>
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> >
> >
This will copy Row 10, from Column A, to as many columns as you drag down
the formula:
..
=INDEX($10:$10,,ROW(A1))
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
"George A. Yorks" <[email protected].(donotspam)> wrote in message
news:[email protected]...
> A new question.
> I am able to copy data from column of numbers to a corresponding column
> using the following basic formula =b11 to represent column b row 11. In
the
> corresponding column I can drag the first cell data down the column and
all
> data is transfered. I there a formula to use to data from rows to columns
> using the same concept. I am looking to have the column heading change
and
> the data row remaing the same.
> --
> George
>
>
> "George A. Yorks" wrote:
>
> > I would like to send the sheet to you however all I have is a list of
names
> > and dollar amounts. This represent earnings that I am tracking. There
is
> > the additional letter as a trailer to some of the names.(this designates
> > participation in an additional group. The use of clean function does
> > deletethe letter but the hidden space remains. The use of the trim
function
> > can not correct this situation. I copy the list from the web. If you
can
> > suggest how I might send it to you I will be pleasedto do so. Or any
other
> > suggestions you might have
> > --
> > George
> >
> >
> > "Ragdyer" wrote:
> >
> > > If you wish, you can send me your sheet, and I'll see what I can
figure out.
> > >
> > > Cut out cutout from my address.
> > > --
> > > Regards,
> > >
> > > RD
> > >
> >
> --------------------------------------------------------------------------
-
> > > Please keep all correspondence within the NewsGroup, so all may
benefit !
> >
> --------------------------------------------------------------------------
-
> > > "George A. Yorks" <[email protected].(donotspam)> wrote in message
> > > news:[email protected]...
> > > > I have tried to use the suggested formula (=code(mid(a1,5,1,)) and
in case
> > > > one received 32 as result. when I then go to search replace I have
> > > entered
> > > > spaces to search and spaces to replace( four blank spaces to search
and
> > > two
> > > > blank spaces to replace.)This does not work and can't figure how
else to
> > > use
> > > > the search replace. Any further help appreciated
> > > > --
> > > > George
> > > >
> > > >
> > > > "RagDyer" wrote:
> > > >
> > > > > I would guess that perhaps the data that's in AX3 doesn't
*exactly*
> > > match
> > > > > what's in Column B.
> > > > >
> > > > > Do you import any of your data?
> > > > > Are the names "full" names, first, and/or middle and last names,
where
> > > there
> > > > > might be a possibilty that the spaces between them might not be a
normal
> > > > > Char(32) space?
> > > > > Could there be a possibility of leading and/or trailing spaces?
> > > > >
> > > > > For a test, key a name into Column B.
> > > > > Enter that same name in *exactly* the same way into AX3, and see
if you
> > > get
> > > > > a correct return from your formula.
> > > > > --
> > > > >
> > > > >
> > > > > Regards,
> > > > >
> > > > > RD
> > > >
> --------------------------------------------------------------------
> > > > > Please keep all correspondence within the Group, so all may
benefit!
> > > >
> -------------------------------------------------------------------
> > > > >
> > > > > "George A. Yorks" <[email protected].(donotspam)> wrote in
message
> > > > > news:[email protected]...
> > > > > I tried this formula and get a "not found" also this is how the
formula
> > > gets
> > > > > entered. Don't know why. It was entered as below.
> > > > > =IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
> > > > >
> > >
found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18
> > > > > ,0)))
> > > > > "RagDyeR" wrote:
> > > > >
> > > > > > Try this in Ay3 of "Miriam":
> > > > > >
> > > > > > =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
> > > > > > Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$18,0)))
> > > > > >
> > > > > > You can then *double click* on the "fill handle" in the lower
right
> > > corner
> > > > > > of AY3, which will *automatically* copy the formula in AY3 down
Column
> > > AY,
> > > > > > as far as there is data in Column AX.
> > > > > > --
> > > > > >
> > > > > > HTH,
> > > > > >
> > > > > > RD
> > > > > > ==============================================
> > > > > > Please keep all correspondence within the Group, so all may
benefit!
> > > > > > ==============================================
> > > > > >
> > > > > >
> > > > > > "George A. Yorks" <[email protected].(donotspam)> wrote in
message
> > > > > > news:[email protected]...
> > > > > > Thanks for the help. I am confronted with a new scenario which
I'll
> > > > > present
> > > > > > for help.
> > > > > >
> > > > > > One worksheet titled geo I have a list of names b4 thru b18 and
data
> > > in
> > > > > > k4thru k18. I want to transfere the data to worksheet titled
miriam
> > > after
> > > > > > searching for the exact names. The names of the second worksheet
are
> > > > > column
> > > > > > ax3 thru ax89. Hope this makes sense to you.
> > > > > >
> > > > > > George
> > > > > >
> > > > > > "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)),"",VLOOKUP(A1,
> > > > > > > 'Sheet2'!$A$1:$D$10, 4, 0)
> > > > > > > --
> > > > > > >
> > > > > > > HTH,
> > > > > > >
> > > > > > > RD
> > > > > > > ==============================================
> > > > > > > Please keep all correspondence within the Group, so all may
benefit!
> > > > > > > ==============================================
> > > > > > >
> > > > > > >
> > > > > > > "George A. Yorks" <[email protected].(donotspam)> wrote in
message
> > > > > > > news:[email protected]...
> > > > > > > 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" <[email protected].(donotspam)> wrote in
> > > message
> > > > > > > > news:[email protected]...
> > > > > > > > > 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" <[email protected].(donotspam)> wrote
in
> > > message
> > > > > > > > >>
news:[email protected]...
> > > > > > > > >> > 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" <[email protected].(donotspam)>
wrote in
> > > > > > message
> > > > > > > > >> > >
news:[email protected]...
> > > > > > > > >> > > > 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
> > > > > > > > >> > >
> > > > > > > > >> > >
> > > > > > > > >> > >
> > > > > > > > >>
> > > > > > > > >>
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > >
> > >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks