creating a formul

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" <gyorks@comcast.net.(donotspam)> wrote in message
news:8F924F5C-856D-48A0-9D3C-9BF538748E3D@microsoft.com...
> 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" <gyorks@comcast.net.(donotspam)> wrote in message
>> news:85A106A1-3EAC-45B1-81F1-E47139806E7B@microsoft.com...
>> > 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" <gyorks@comcast.net.(donotspam)> wrote in message
>> > > news:316947CC-1088-49F7-AEE2-3E81DE914D9D@microsoft.com...
>> > > > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> news:8F924F5C-856D-48A0-9D3C-9BF538748E3D@microsoft.com...
> > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> >> news:85A106A1-3EAC-45B1-81F1-E47139806E7B@microsoft.com...
> >> > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> >> > > news:316947CC-1088-49F7-AEE2-3E81DE914D9D@microsoft.com...
> >> > > > 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" <gyorks@comcast.net.(donotspam)> wrote in message
news:D717F2F0-DDE4-485A-9909-E3CFF61BC1F9@microsoft.com...
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" <gyorks@comcast.net.(donotspam)> wrote in message
> news:8F924F5C-856D-48A0-9D3C-9BF538748E3D@microsoft.com...
> > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> >> news:85A106A1-3EAC-45B1-81F1-E47139806E7B@microsoft.com...
> >> > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> >> > > news:316947CC-1088-49F7-AEE2-3E81DE914D9D@microsoft.com...
> >> > > > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> news:D717F2F0-DDE4-485A-9909-E3CFF61BC1F9@microsoft.com...
> 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > news:8F924F5C-856D-48A0-9D3C-9BF538748E3D@microsoft.com...
> > > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > >> news:85A106A1-3EAC-45B1-81F1-E47139806E7B@microsoft.com...
> > >> > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > >> > > news:316947CC-1088-49F7-AEE2-3E81DE914D9D@microsoft.com...
> > >> > > > 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" <gyorks@comcast.net.(donotspam)> wrote in message
news:C6666303-FE4A-4D80-B53B-33EB0235C6EE@microsoft.com...
> 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > news:D717F2F0-DDE4-485A-9909-E3CFF61BC1F9@microsoft.com...
> > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > > news:8F924F5C-856D-48A0-9D3C-9BF538748E3D@microsoft.com...
> > > > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > > >> news:85A106A1-3EAC-45B1-81F1-E47139806E7B@microsoft.com...
> > > >> > 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" <gyorks@comcast.net.(donotspam)> wrote in
message
> > > >> > > news:316947CC-1088-49F7-AEE2-3E81DE914D9D@microsoft.com...
> > > >> > > > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> news:C6666303-FE4A-4D80-B53B-33EB0235C6EE@microsoft.com...
> > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > > news:D717F2F0-DDE4-485A-9909-E3CFF61BC1F9@microsoft.com...
> > > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > > > news:8F924F5C-856D-48A0-9D3C-9BF538748E3D@microsoft.com...
> > > > > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > > > >> news:85A106A1-3EAC-45B1-81F1-E47139806E7B@microsoft.com...
> > > > >> > 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" <gyorks@comcast.net.(donotspam)> wrote in
> message
> > > > >> > > news:316947CC-1088-49F7-AEE2-3E81DE914D9D@microsoft.com...
> > > > >> > > > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> news:D717F2F0-DDE4-485A-9909-E3CFF61BC1F9@microsoft.com...
> 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > news:8F924F5C-856D-48A0-9D3C-9BF538748E3D@microsoft.com...
> > > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > >> news:85A106A1-3EAC-45B1-81F1-E47139806E7B@microsoft.com...
> > >> > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > >> > > news:316947CC-1088-49F7-AEE2-3E81DE914D9D@microsoft.com...
> > >> > > > 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" <gyorks@comcast.net.(donotspam)> wrote in message
news:2B948C21-AD25-4713-A551-A6ED3965591B@microsoft.com...
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" <gyorks@comcast.net.(donotspam)> wrote in message
> news:D717F2F0-DDE4-485A-9909-E3CFF61BC1F9@microsoft.com...
> 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > news:8F924F5C-856D-48A0-9D3C-9BF538748E3D@microsoft.com...
> > > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > >> news:85A106A1-3EAC-45B1-81F1-E47139806E7B@microsoft.com...
> > >> > 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" <gyorks@comcast.net.(donotspam)> wrote in
message
> > >> > > news:316947CC-1088-49F7-AEE2-3E81DE914D9D@microsoft.com...
> > >> > > > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> news:2B948C21-AD25-4713-A551-A6ED3965591B@microsoft.com...
> 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > news:D717F2F0-DDE4-485A-9909-E3CFF61BC1F9@microsoft.com...
> > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > > news:8F924F5C-856D-48A0-9D3C-9BF538748E3D@microsoft.com...
> > > > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > > >> news:85A106A1-3EAC-45B1-81F1-E47139806E7B@microsoft.com...
> > > >> > 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" <gyorks@comcast.net.(donotspam)> wrote in
> message
> > > >> > > news:316947CC-1088-49F7-AEE2-3E81DE914D9D@microsoft.com...
> > > >> > > > 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" <gyorks@comcast.net.(donotspam)> wrote in message
news:48E736C3-3436-431F-A2CB-7CB40D09F2CA@microsoft.com...
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" <gyorks@comcast.net.(donotspam)> wrote in message
> news:2B948C21-AD25-4713-A551-A6ED3965591B@microsoft.com...
> 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > news:D717F2F0-DDE4-485A-9909-E3CFF61BC1F9@microsoft.com...
> > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > > news:8F924F5C-856D-48A0-9D3C-9BF538748E3D@microsoft.com...
> > > > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > > >> news:85A106A1-3EAC-45B1-81F1-E47139806E7B@microsoft.com...
> > > >> > 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" <gyorks@comcast.net.(donotspam)> wrote in
> message
> > > >> > > news:316947CC-1088-49F7-AEE2-3E81DE914D9D@microsoft.com...
> > > >> > > > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > news:2B948C21-AD25-4713-A551-A6ED3965591B@microsoft.com...
> > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > > news:D717F2F0-DDE4-485A-9909-E3CFF61BC1F9@microsoft.com...
> > > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > > > news:8F924F5C-856D-48A0-9D3C-9BF538748E3D@microsoft.com...
> > > > > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > > > >> news:85A106A1-3EAC-45B1-81F1-E47139806E7B@microsoft.com...
> > > > >> > 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" <gyorks@comcast.net.(donotspam)> wrote in
> > message
> > > > >> > > news:316947CC-1088-49F7-AEE2-3E81DE914D9D@microsoft.com...
> > > > >> > > > 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
> > > > >> > >
> > > > >> > >
> > > > >> > >
> > > > >>
> > > > >>
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >