creating a formul

Trying to create a formula to do the following:
Sheet 1 column A a list of personal names a1-a10

Sheet 2 has list of names a1-a10 and list of dollar amounts colums d1-d10

want to search sheet one and if any name from sheet 2 found on sheet 1 than
the corresponding dollar amount is entered.

Any help appreciated.
--
George

I bet you want to use =vlookup().

Debra Dalgleish has some nice instructions at:
http://www.contextures.com/xlFunctions02.html

George A. Yorks wrote:
>
> Trying to create a formula to do the following:
> Sheet 1 column A a list of personal names a1-a10
>
> Sheet 2 has list of names a1-a10 and list of dollar amounts colums d1-d10
>
> want to search sheet one and if any name from sheet 2 found on sheet 1 than
> the corresponding dollar amount is entered.
>
> Any help appreciated.
> --
> George

--

Dave Peterson

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

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

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've used the following formula =vlookup(b4,sheet3!$c$1:$m$31,11,0) The
correct data is transfered to the first cell in the column all other cells
receive #N/A a value is not available to the formula or function. There is
however data to be transfered.Any help appreciated

"George A. Yorks" wrote:

> Thanks for the help. One further question.
> In the formul In sheet 2 $A$1:$b$10makes reference to two adjacent columns.
> If there is a column between so there is data in a and c and none in b the
> result return err,the formula in this cell referes to cells that are
> currently emply. How to get around this. ie" if sheet two has data in column
> a and column d.
>
> thanks
>
> George Yorks
>
> "Earl Kiosterud" wrote:
>
> > George,
> >
> > In B1 of sheet 1:
> > =VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE)
> >
> > Copy down with fill handle to B10.
> > --
> > Earl Kiosterud
> > mvpearl omitthisword at verizon period net
> > -------------------------------------------
> >
> > "George A. Yorks" <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
> >
> >
> >

Well, Excel isn't seeing a match. For one of the formulas that you expect to
return a value, what is in column B, and what is the matching data in column C
or your table on Sheet3? Could the problem be that you have numbers in one
place and text that looks like a number (but is stored as text) in the other?
If so, they won't match, e.g. 1 doesn't match "1"


On Mon, 21 Feb 2005 13:55:04 -0800, George A. Yorks
<gyorks@comcast.net.(donotspam)> wrote:

>I've used the following formula =vlookup(b4,sheet3!$c$1:$m$31,11,0) The
>correct data is transfered to the first cell in the column all other cells
>receive #N/A a value is not available to the formula or function. There is
>however data to be transfered.Any help appreciated
>
>"George A. Yorks" wrote:
>
>> Thanks for the help. One further question.
>> In the formul In sheet 2 $A$1:$b$10makes reference to two adjacent columns.
>> If there is a column between so there is data in a and c and none in b the
>> result return err,the formula in this cell referes to cells that are
>> currently emply. How to get around this. ie" if sheet two has data in
column
>> a and column d.
>>
>> thanks
>>
>> George Yorks
>>
>> "Earl Kiosterud" wrote:
>>
>> > George,
>> >
>> > In B1 of sheet 1:
>> > =VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE)
>> >
>> > Copy down with fill handle to B10.
>> > --
>> > Earl Kiosterud
>> > mvpearl omitthisword at verizon period net
>> > -------------------------------------------
>> >
>> > "George A. Yorks" <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
>> >
>> >
>> >

Ihave tried to copy data from USA Today report. When I past to excel
worksheet all cells are obliterated. Does this account for the data not
being recognized. If so is there anything that can be done to make this data
useable??

Thanks for all the help and information

"Myrna Larson" wrote:

> Well, Excel isn't seeing a match. For one of the formulas that you expect to
> return a value, what is in column B, and what is the matching data in column C
> or your table on Sheet3? Could the problem be that you have numbers in one
> place and text that looks like a number (but is stored as text) in the other?
> If so, they won't match, e.g. 1 doesn't match "1"
>
>
> On Mon, 21 Feb 2005 13:55:04 -0800, George A. Yorks
> <gyorks@comcast.net.(donotspam)> wrote:
>
> >I've used the following formula =vlookup(b4,sheet3!$c$1:$m$31,11,0) The
> >correct data is transfered to the first cell in the column all other cells
> >receive #N/A a value is not available to the formula or function. There is
> >however data to be transfered.Any help appreciated
> >
> >"George A. Yorks" wrote:
> >
> >> Thanks for the help. One further question.
> >> In the formul In sheet 2 $A$1:$b$10makes reference to two adjacent columns.
> >> If there is a column between so there is data in a and c and none in b the
> >> result return err,the formula in this cell referes to cells that are
> >> currently emply. How to get around this. ie" if sheet two has data in
> column
> >> a and column d.
> >>
> >> thanks
> >>
> >> George Yorks
> >>
> >> "Earl Kiosterud" wrote:
> >>
> >> > George,
> >> >
> >> > In B1 of sheet 1:
> >> > =VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE)
> >> >
> >> > Copy down with fill handle to B10.
> >> > --
> >> > Earl Kiosterud
> >> > mvpearl omitthisword at verizon period net
> >> > -------------------------------------------
> >> >
> >> > "George A. Yorks" <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
> >> >
> >> >
> >> >
>
>

You will need to use the vlookup formula. Have a look at
this in the help menu. It's quite simple, and compares to
lists to return a value.

=vlookup(a1:a10,B1:c10,2,false)

In this case, it looks up the values in cells a1 to a10
and compares them to the values in cells b1 to b10 and
returns the second value to thr right of these cells;
that is, the values in column c.

Hope that helps.

>-----Original Message-----
>Trying to create a formula to do the following:
>Sheet 1 column A a list of personal names a1-a10
>
>Sheet 2 has list of names a1-a10 and list of dollar
amounts colums d1-d10
>
>want to search sheet one and if any name from sheet 2
found on sheet 1 than
>the corresponding dollar amount is entered.
>
>Any help appreciated.
>--
>George
>.
>

Thank you for all the help. I am experiencing one little problem. I write
the formula into cell E1 and attempt to copy down to the last cell using the
fill handle. I seems as though every second cell comes up with #N/A. Any
ideas what is causing this.

Again thank you for all the help

"James" wrote:

> You will need to use the vlookup formula. Have a look at
> this in the help menu. It's quite simple, and compares to
> lists to return a value.
>
> =vlookup(a1:a10,B1:c10,2,false)
>
> In this case, it looks up the values in cells a1 to a10
> and compares them to the values in cells b1 to b10 and
> returns the second value to thr right of these cells;
> that is, the values in column c.
>
> Hope that helps.
>
> >-----Original Message-----
> >Trying to create a formula to do the following:
> >Sheet 1 column A a list of personal names a1-a10
> >
> >Sheet 2 has list of names a1-a10 and list of dollar
> amounts colums d1-d10
> >
> >want to search sheet one and if any name from sheet 2
> found on sheet 1 than
> >the corresponding dollar amount is entered.
> >
> >Any help appreciated.
> >--
> >George
> >.
> >
>

This is an array formula (since the 1st argument to VLOOKUP is not a single
cell, but 10 cells). I would also change the references to absolute, i.e.

=VLOOKUP($A$1:$A$10,$B$1:$C$10,2,0)

Then, to use this, select the 10 cells, E1:E10, and enter the above formula in
E1, and press CTRL+SHIFT+ENTER to enter it as an array formula into all 10
cells.

Note that you don't enter it in the top and copy it down. That would just keep
returning the 1st result rather than all 10.


On Sat, 19 Feb 2005 14:25:02 -0800, George A. Yorks
<gyorks@comcast.net.(donotspam)> wrote:

>Thank you for all the help. I am experiencing one little problem. I write
>the formula into cell E1 and attempt to copy down to the last cell using the
>fill handle. I seems as though every second cell comes up with #N/A. Any
>ideas what is causing this.
>
>Again thank you for all the help
>
>"James" wrote:
>
>> You will need to use the vlookup formula. Have a look at
>> this in the help menu. It's quite simple, and compares to
>> lists to return a value.
>>
>> =vlookup(a1:a10,B1:c10,2,false)
>>
>> In this case, it looks up the values in cells a1 to a10
>> and compares them to the values in cells b1 to b10 and
>> returns the second value to thr right of these cells;
>> that is, the values in column c.
>>
>> Hope that helps.
>>
>> >-----Original Message-----
>> >Trying to create a formula to do the following:
>> >Sheet 1 column A a list of personal names a1-a10
>> >
>> >Sheet 2 has list of names a1-a10 and list of dollar
>> amounts colums d1-d10
>> >
>> >want to search sheet one and if any name from sheet 2
>> found on sheet 1 than
>> >the corresponding dollar amount is entered.
>> >
>> >Any help appreciated.
>> >--
>> >George
>> >.
>> >
>>

I don't understand the advantage to using an array formula in this
particular case.

Can anyone please explain to me the difference between:

=VLOOKUP(A1,$B$1:$C$10,2,0)
(Regular <Enter> - drag down to copy)
AND
=VLOOKUP($A$1:$A$10,$B$1:$C$10,2,0)
Array entered, <C,S,E>, where you have to first pre-select the rows, and
enter the formula in the top focus cell?

Am I missing something?
--
Regards,

RD

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

"Myrna Larson" <anonymous@discussions.microsoft.com> wrote in message
news:h7gf11ht9sv2thto7dt6gmqp5go7o9l8m3@4ax.com...
> This is an array formula (since the 1st argument to VLOOKUP is not a
single
> cell, but 10 cells). I would also change the references to absolute, i.e.
>
> =VLOOKUP($A$1:$A$10,$B$1:$C$10,2,0)
>
> Then, to use this, select the 10 cells, E1:E10, and enter the above
formula in
> E1, and press CTRL+SHIFT+ENTER to enter it as an array formula into all 10
> cells.
>
> Note that you don't enter it in the top and copy it down. That would just
keep
> returning the 1st result rather than all 10.
>
>
> On Sat, 19 Feb 2005 14:25:02 -0800, George A. Yorks
> <gyorks@comcast.net.(donotspam)> wrote:
>
> >Thank you for all the help. I am experiencing one little problem. I
write
> >the formula into cell E1 and attempt to copy down to the last cell using
the
> >fill handle. I seems as though every second cell comes up with #N/A.
Any
> >ideas what is causing this.
> >
> >Again thank you for all the help
> >
> >"James" wrote:
> >
> >> You will need to use the vlookup formula. Have a look at
> >> this in the help menu. It's quite simple, and compares to
> >> lists to return a value.
> >>
> >> =vlookup(a1:a10,B1:c10,2,false)
> >>
> >> In this case, it looks up the values in cells a1 to a10
> >> and compares them to the values in cells b1 to b10 and
> >> returns the second value to thr right of these cells;
> >> that is, the values in column c.
> >>
> >> Hope that helps.
> >>
> >> >-----Original Message-----
> >> >Trying to create a formula to do the following:
> >> >Sheet 1 column A a list of personal names a1-a10
> >> >
> >> >Sheet 2 has list of names a1-a10 and list of dollar
> >> amounts colums d1-d10
> >> >
> >> >want to search sheet one and if any name from sheet 2
> >> found on sheet 1 than
> >> >the corresponding dollar amount is entered.
> >> >
> >> >Any help appreciated.
> >> >--
> >> >George
> >> >.
> >> >
> >>
>

I'm not sure there is any advantage. My reply was targeted at the formula he
says he found in Help, in which the first argument was not a single cell, but
A1:A10. I haven't looked at it myself. Maybe he didn't understand the example
in Help and created the formula incorrectly.


On Sat, 19 Feb 2005 16:12:51 -0800, "Ragdyer" <RagDyer@cutoutmsn.com> wrote:

>I don't understand the advantage to using an array formula in this
>particular case.
>
>Can anyone please explain to me the difference between:
>
>=VLOOKUP(A1,$B$1:$C$10,2,0)
>(Regular <Enter> - drag down to copy)
>AND
>=VLOOKUP($A$1:$A$10,$B$1:$C$10,2,0)
>Array entered, <C,S,E>, where you have to first pre-select the rows, and
>enter the formula in the top focus cell?
>
>Am I missing something?