#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default VLOOKUP formula

I have two spreadsheets that I need to merge and am using a VLOOKUP formula.
Sheet 1 has ID numbers in column A; Sheet 2 has ID numbers in column A, last
names in column B, and first names in column C. I need Sheet 2 to merge into
Sheet 1, putting the correct name with the corresponding ID number.

I have a VLOOKUP formula in cell B1 Sheet1. What's my next step? The
formula is =VLOOKUP(A1,Sheet2!$A$1:$D$1000,2,FALSE)
--
Briggs
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,805
Default VLOOKUP formula

Your formula or
=VLOOKUP(A1,Sheet2!$A$1:$C$1000,2,FALSE)
since Col D is not really required will give you last names in Col B for
those IDs which are present in Sheet2 and #N/A for those which are not.
Similarly
=VLOOKUP(A1,Sheet2!$A$1:$C$1000,3,FALSE)
will give you the first names...

If you want to see which IDs are there in Sheet 2 and Not in Sheet1 you can
have the reverse formaul in Sheet2 Col D like
=VLOOKUP(A1,Sheet1!$A$1:$A$1000,1,FALSE)
#N/A will give you the Ids which are not in Sheet 1...

You can filter and copy those on to Sheet 1..

Once you are done you can convert your formulae to values by Copying and then
PASTE SPECIAL|Values..

Hope that is what you wanted...



"Briggs" wrote:

I have two spreadsheets that I need to merge and am using a VLOOKUP formula.
Sheet 1 has ID numbers in column A; Sheet 2 has ID numbers in column A, last
names in column B, and first names in column C. I need Sheet 2 to merge into
Sheet 1, putting the correct name with the corresponding ID number.

I have a VLOOKUP formula in cell B1 Sheet1. What's my next step? The
formula is =VLOOKUP(A1,Sheet2!$A$1:$D$1000,2,FALSE)
--
Briggs

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default VLOOKUP formula

You've been a huge help, but I'm really a novice at this and what I don't
understand is the actual mechanics. If my formula is in cell B1 in sheet 1,
what do I copy and paste special.....specifically, what rows/columns do I
select on what sheet? sorry to be so confused. I'm almost getting this, but
am stuck on what I copy and paste to where. Thanks for your help!
--
Briggs


"Sheeloo" wrote:

Your formula or
=VLOOKUP(A1,Sheet2!$A$1:$C$1000,2,FALSE)
since Col D is not really required will give you last names in Col B for
those IDs which are present in Sheet2 and #N/A for those which are not.
Similarly
=VLOOKUP(A1,Sheet2!$A$1:$C$1000,3,FALSE)
will give you the first names...

If you want to see which IDs are there in Sheet 2 and Not in Sheet1 you can
have the reverse formaul in Sheet2 Col D like
=VLOOKUP(A1,Sheet1!$A$1:$A$1000,1,FALSE)
#N/A will give you the Ids which are not in Sheet 1...

You can filter and copy those on to Sheet 1..

Once you are done you can convert your formulae to values by Copying and then
PASTE SPECIAL|Values..

Hope that is what you wanted...



"Briggs" wrote:

I have two spreadsheets that I need to merge and am using a VLOOKUP formula.
Sheet 1 has ID numbers in column A; Sheet 2 has ID numbers in column A, last
names in column B, and first names in column C. I need Sheet 2 to merge into
Sheet 1, putting the correct name with the corresponding ID number.

I have a VLOOKUP formula in cell B1 Sheet1. What's my next step? The
formula is =VLOOKUP(A1,Sheet2!$A$1:$D$1000,2,FALSE)
--
Briggs

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,805
Default VLOOKUP formula

Don't feel bad about not understanding... you will be an expert soon...

There are two parts...
One is getting the formula right... hope you got that.

Second is to convert the formula to values... this is optional. If you do
this then you are no longer dependent on the other sheet...

Suppose you have formulas in Col B and you want to convert them to the
values they represent...
Then select Col B, Copy and PASTE SPECIAL|Values on Col B itself.


Hope this makes sense...

"Briggs" wrote:

You've been a huge help, but I'm really a novice at this and what I don't
understand is the actual mechanics. If my formula is in cell B1 in sheet 1,
what do I copy and paste special.....specifically, what rows/columns do I
select on what sheet? sorry to be so confused. I'm almost getting this, but
am stuck on what I copy and paste to where. Thanks for your help!
--
Briggs


"Sheeloo" wrote:

Your formula or
=VLOOKUP(A1,Sheet2!$A$1:$C$1000,2,FALSE)
since Col D is not really required will give you last names in Col B for
those IDs which are present in Sheet2 and #N/A for those which are not.
Similarly
=VLOOKUP(A1,Sheet2!$A$1:$C$1000,3,FALSE)
will give you the first names...

If you want to see which IDs are there in Sheet 2 and Not in Sheet1 you can
have the reverse formaul in Sheet2 Col D like
=VLOOKUP(A1,Sheet1!$A$1:$A$1000,1,FALSE)
#N/A will give you the Ids which are not in Sheet 1...

You can filter and copy those on to Sheet 1..

Once you are done you can convert your formulae to values by Copying and then
PASTE SPECIAL|Values..

Hope that is what you wanted...



"Briggs" wrote:

I have two spreadsheets that I need to merge and am using a VLOOKUP formula.
Sheet 1 has ID numbers in column A; Sheet 2 has ID numbers in column A, last
names in column B, and first names in column C. I need Sheet 2 to merge into
Sheet 1, putting the correct name with the corresponding ID number.

I have a VLOOKUP formula in cell B1 Sheet1. What's my next step? The
formula is =VLOOKUP(A1,Sheet2!$A$1:$D$1000,2,FALSE)
--
Briggs

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Alternative formula to the vlookup formula? Victor Excel Worksheet Functions 2 May 12th 08 04:38 PM
convert vlookup formula to link formula AFA Excel Worksheet Functions 0 February 20th 08 05:24 AM
Excel 2002 VLOOKUP formula or other formula Serge Excel Discussion (Misc queries) 4 February 26th 07 04:56 PM
vlookup formula alpa Excel Worksheet Functions 1 October 20th 05 12:14 AM
Help with vlookup formula sarahp Excel Worksheet Functions 2 September 28th 05 05:21 PM


All times are GMT +1. The time now is 06:21 PM.

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

About Us

"It's about Microsoft Excel"