A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

creating a formul



 
 
Thread Tools Display Modes
  #1  
Old February 16th, 2005, 04:33 AM
George A. Yorks
external usenet poster
 
Posts: n/a
Default 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
  #2  
Old February 16th, 2005, 05:11 AM
Dave Peterson
external usenet poster
 
Posts: n/a
Default

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
  #3  
Old February 16th, 2005, 05:15 AM
Earl Kiosterud
external usenet poster
 
Posts: n/a
Default

George,

In B1 of sheet 1:
=VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE)

Copy down with fill handle to B10.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George A. Yorks" .(donotspam) wrote in message
...
Trying to create a formula to do the following:
Sheet 1 column A a list of personal names a1-a10

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

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

Any help appreciated.
--
George



  #4  
Old February 16th, 2005, 05:17 AM
James
external usenet poster
 
Posts: n/a
Default

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
.

  #5  
Old February 19th, 2005, 11:25 PM
George A. Yorks
external usenet poster
 
Posts: n/a
Default

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
.


  #6  
Old February 19th, 2005, 11:47 PM
Myrna Larson
external usenet poster
 
Posts: n/a
Default

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



  #7  
Old February 20th, 2005, 01:12 AM
Ragdyer
external usenet poster
 
Posts: n/a
Default

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" wrote in message
...
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
.(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
.




  #8  
Old February 20th, 2005, 05:20 AM
Myrna Larson
external usenet poster
 
Posts: n/a
Default

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


  #9  
Old February 20th, 2005, 07:27 PM
George A. Yorks
external usenet poster
 
Posts: n/a
Default

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

thanks

George Yorks

"Earl Kiosterud" wrote:

George,

In B1 of sheet 1:
=VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE)

Copy down with fill handle to B10.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George A. Yorks" .(donotspam) wrote in message
...
Trying to create a formula to do the following:
Sheet 1 column A a list of personal names a1-a10

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

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

Any help appreciated.
--
George




  #10  
Old February 20th, 2005, 07:38 PM
Ragdyer
external usenet poster
 
Posts: n/a
Default

Try this:

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

--
HTH,

RD

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

"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. One further question.
In the formul In sheet 2 $A$1:$b$10makes reference to two adjacent

columns.
If there is a column between so there is data in a and c and none in b the
result return err,the formula in this cell referes to cells that are
currently emply. How to get around this. ie" if sheet two has data in

column
a and column d.

thanks

George Yorks

"Earl Kiosterud" wrote:

George,

In B1 of sheet 1:
=VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE)

Copy down with fill handle to B10.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George A. Yorks" .(donotspam) wrote in message
...
Trying to create a formula to do the following:
Sheet 1 column A a list of personal names a1-a10

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

d1-d10

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

Any help appreciated.
--
George





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Mail Merge for Creating a Document of Biographies... Sam Clarke General Discussion 1 January 4th, 2005 05:28 PM
Using Mail Merge for Creating a Document of Biographies... Sam Clarke Mailmerge 1 December 17th, 2004 11:17 AM
Using Mail Merge for Creating a Document of Biographies... Sam Clarke General Discussions 0 December 16th, 2004 08:35 PM
Creating Forms using VBA Suzi General Discussion 1 November 18th, 2004 04:46 PM
Problem Creating Report [email protected] Using Forms 1 June 7th, 2004 07:51 AM


All times are GMT +1. The time now is 09:57 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.