creating a formul

[Guest]

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???

 

[Guest]

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]

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!
==============================================


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

 

[Guest]

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]

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!
-------------------------------------------------------------------

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)))

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!
==============================================


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

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!
==============================================


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

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
-------------------------------------------

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?

:

Try this:

=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)

--
HTH,

RD

benefit

!

-------------------------------------------------------------------------
--

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

:

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
-------------------------------------------

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.

 

[Guest]

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

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)))

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!
==============================================


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

 

[Myrna Larson]

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.

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

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)))

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!
==============================================


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

:

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!
==============================================


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

:

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
-------------------------------------------

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?

:

Try this:

=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)

--
HTH,

RD



-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may
benefit
!


-------------------------------------------------------------------------
--

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

:

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
-------------------------------------------

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.

 

[Guest]

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

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.

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

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)))
:

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!
==============================================


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

:

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!
==============================================


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

:

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
-------------------------------------------

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?

:

Try this:

=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)

--
HTH,

RD



-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may
benefit
!


-------------------------------------------------------------------------
--

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

:

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
-------------------------------------------

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.

 

[Dave Peterson]

Look under Edit|Replace on the worksheet toolbar.

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

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.

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

:

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)))
:

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!
==============================================


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

:

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!
==============================================


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

:

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
-------------------------------------------

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?

:

Try this:

=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)

--
HTH,

RD



-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may
benefit
!


-------------------------------------------------------------------------
--

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

:

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
-------------------------------------------

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.

 

[Guest]

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

Look under Edit|Replace on the worksheet toolbar.

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

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

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

:

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)))
:

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!
==============================================


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

:

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!
==============================================


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

:

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
-------------------------------------------

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?

:

Try this:

=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)

--
HTH,

RD




-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may
benefit
!



-------------------------------------------------------------------------
--

message
Thanks for the help. One further question.
In the formul In sheet 2 $A$1:$b$10makes reference to two
adjacent
columns.
If there is a column between so there is data in a and c and
none
in
b
the
result return err,the formula in this cell referes to cells that
are
currently emply. How to get around this. ie" if sheet two has
data
in
column
a and column d.

thanks

George Yorks

:

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
-------------------------------------------

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.

 

[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()")

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

Look under Edit|Replace on the worksheet toolbar.

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

:

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

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

:

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)))
:

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!
==============================================


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

:

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!
==============================================


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

:

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
-------------------------------------------

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?

:

Try this:

=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)

--
HTH,

RD




-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may
benefit
!



-------------------------------------------------------------------------
--

message
Thanks for the help. One further question.
In the formul In sheet 2 $A$1:$b$10makes reference to two
adjacent
columns.
If there is a column between so there is data in a and c and
none
in
b
the
result return err,the formula in this cell referes to cells that
are
currently emply. How to get around this. ie" if sheet two has
data
in
column
a and column d.

thanks

George Yorks

:

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
-------------------------------------------

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.

 

[Guest]

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

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

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.

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

:

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)))
:

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!
==============================================


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

:

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!
==============================================


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

:

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
-------------------------------------------

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?

:

Try this:

=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)

--
HTH,

RD



-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may
benefit
!


-------------------------------------------------------------------------
--

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

:

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
-------------------------------------------

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.

 

[Bob Phillips]

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

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

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

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

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

:

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)))

 

[Guest]

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

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

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

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

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

:

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)))
:

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!
==============================================


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

:

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!
==============================================


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

:

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
-------------------------------------------

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?

:

Try this:

=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)

--
HTH,

RD




-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may
benefit
!



-------------------------------------------------------------------------
--

message
Thanks for the help. One further question.
In the formul In sheet 2 $A$1:$b$10makes reference to two
adjacent
columns.
If there is a column between so there is data in a and c and
none
in
b
the
result return err,the formula in this cell referes to cells that
are
currently emply. How to get around this. ie" if sheet two has
data
in
column
a and column d.

thanks

George Yorks

:

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
-------------------------------------------

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.

 

[RagDyer]

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!
==============================================

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

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

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

:

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

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

:

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)))

 

[Guest]

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

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!
-------------------------------------------------------------------

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)))

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!
==============================================


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]

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 !
---------------------------------------------------------------------------

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

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!
-------------------------------------------------------------------

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)))

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!
==============================================


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

:

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!
==============================================


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

:

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
-------------------------------------------

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?

:

Try this:

=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)

--
HTH,

RD




-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may
benefit
!



-------------------------------------------------------------------------
--

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

:

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
-------------------------------------------

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.

 

[Guest]

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

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 !
---------------------------------------------------------------------------

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

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!
-------------------------------------------------------------------

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)))
:

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!
==============================================


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

:

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!
==============================================


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

:

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
-------------------------------------------

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?

:

Try this:

=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)

--
HTH,

RD





-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may
benefit
!




-------------------------------------------------------------------------
--

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

:

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
-------------------------------------------

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.

 

[RagDyer]

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!
-------------------------------------------------------------------

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

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

!

--------------------------------------------------------------------------

-

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


:

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

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)))
:

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!
==============================================


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

:

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!
==============================================


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

:

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
-------------------------------------------

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?

:

Try this:

=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)

--
HTH,

RD





-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may
benefit
!




-------------------------------------------------------------------------
--

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

:

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
-------------------------------------------

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.

 

[Guest]

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

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!
-------------------------------------------------------------------

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

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

!

--------------------------------------------------------------------------

-

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


:

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!
-------------------------------------------------------------------

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)))
:

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!
==============================================


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

:

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!
==============================================


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

:

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
-------------------------------------------

message
Thanks for the help. I know I'll fully understand the formula
structure
soon. One question, at end of formula ,4,0 what in fact does
the 4
make
reference to?

:

Try this:

=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)

--
HTH,

RD






-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may
benefit
!





-------------------------------------------------------------------------
--

message
Thanks for the help. One further question.
In the formul In sheet 2 $A$1:$b$10makes reference to two
adjacent
columns.
If there is a column between so there is data in a and c and
none
in
b
the
result return err,the formula in this cell referes to cells
that
are
currently emply. How to get around this. ie" if sheet