return the staff # to the right of the names


I have a workbook with 2 sheets, one sheet named “sheet1”, another named “sheet2”. 
In sheet 1, I have 2 columns. Column A is the names, column B is the Staff #.s
	A		B
1	Name		Staff Number
2	Tom		235
3	Jack		345
4	Linda		888
…
In sheet 2, I have the names in column A, but the column B is empty:
	A		B
1	Name		Staff Number
2	Jack
3	Linda
4	Joe
…
In sheet 2, column B, I want the computer to return me the correspond staff numbers which are listed in sheet 1: I want 345 in B2, 888 in B3.
		

0
plumstone (35)
8/5/2004 8:23:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
413 Views

Similar Articles

[PageSpeed] 7

See my page on VLOOKUP
  http://www.mvps.org/dmcritchie/excel/vlookup.htm
and please sign your name at the end so we can refer to you by your name.

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Sheet2 B2:
   =VLOOKUP(B1,  sheet1!A$1:B$30,2,0)
use the fill handle to fill  down as needed.

---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"plumstone" <plumstone@discussions.microsoft.com> wrote in message news:1FF0CFB5-5FFC-40B5-AA78-08F8FD127BDA@microsoft.com...
>
>
> I have a workbook with 2 sheets, one sheet named "sheet1", another named "sheet2".
> In sheet 1, I have 2 columns. Column A is the names, column B is the Staff #.s
> A B
> 1 Name Staff Number
> 2 Tom 235
> 3 Jack 345
> 4 Linda 888
> .
> In sheet 2, I have the names in column A, but the column B is empty:
> A B
> 1 Name Staff Number
> 2 Jack
> 3 Linda
> 4 Joe
> .
> In sheet 2, column B, I want the computer to return me the correspond staff numbers which are listed in sheet 1: I want 345 in B2,
888 in B3.
>
>


0
dmcritchie (2586)
8/5/2004 8:34:07 PM
B2 =VLOOKUP(A2,Sheet1!$A$2:$B$4,2,FALSE)
"plumstone" <plumstone@discussions.microsoft.com> wrote in message
news:1FF0CFB5-5FFC-40B5-AA78-08F8FD127BDA@microsoft.com...
>
>
> I have a workbook with 2 sheets, one sheet named "sheet1", another named
"sheet2".
> In sheet 1, I have 2 columns. Column A is the names, column B is the Staff
#.s
> A B
> 1 Name Staff Number
> 2 Tom 235
> 3 Jack 345
> 4 Linda 888
> .
> In sheet 2, I have the names in column A, but the column B is empty:
> A B
> 1 Name Staff Number
> 2 Jack
> 3 Linda
> 4 Joe
> .
> In sheet 2, column B, I want the computer to return me the correspond
staff numbers which are listed in sheet 1: I want 345 in B2, 888 in B3.
>
>


0
news9767 (27)
8/5/2004 8:41:13 PM
Reply:

Similar Artilces:

make a distribution list from a named category(in contact list)
I've created a category name from certain contacts...can I use that category name to directly create a distribution list? What happens now is when I'm adding to the dist. list, the contact list shows up, but the category "field" doesn't show up in the list, so its hard to remember which contact is in which category. Thanks -David If you are using categories, why be redundant and use a DL? Drag the category to your inbox and your distribution is set. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited m...

Save a new file with the name that is in cell "C7"
Here is what I want to do: Open up a template. Enter information. Click on save, and when the window shows up asking me to name the file, I want it to automatically use a file name with the value in cell C7. As an example, if cell C7 has "Milton 287-12", I want to automatically name the file"Milton 287-12" when I save for the first time. Is this possible? Thanks, Joe Hi, Lunker, Try pasting the following code into a macro sheet and running it with Alt-F8 Change the path to whatever you require between the quote in line 3 and "A1" to whatever cell you want...

Insert Comment Function
I am using Excel for our National Guard unit on a LAN system. The spreadsheet I created several of us use but not in a shared mode. Some users when they Insert a Comment in the cell it inputs their name, which we want, some users it simply inserts "USAF User" I assume it looks at who is signed into the LAN but each of us has to do that just to get to the spreadsheet so why does it insert some users names and some it doesn't ? Thanks Excel 2003 uses whatever is in Tools, Options, General, User name. Some companies preset it to the company name. Some users put their pref...

How to merge records into one record by customer's name?
How can I merge repeating records of a customer who has different oders of products? Ist col, Company name, 2nd col cust name, 3rd-10th col Product items Thks, I had tried the help on consolidate, could not fiigure it out. Thks! One way to try .. Assume you have in Sheet1, in A1:E5, the table: Comp Cust Prd#1 Prd#2 Prd#3 ABC XXX 40 50 90 DEF YYY 30 50 20 ABC XXX 50 70 70 DEF YYY 50 30 20 In Sheet2, you have the "master" table below in A1:E2 Comp Cust Prd#1 Prd#2 Prd#3 ABC XXX DEF YYY Put in C2: =SUMPRODUCT((Sheet1!$A$2:$A$5=$A2)*(Sheet1!$B$2:$B$5=$B2),Sheet1!C$2:C$5) Copy C...

cannot obtain provider factory for data provider named 'microsoft.sqlserverce.client3.5'
Hello everybody, I work with VS 2008 on the Windows 7. MS SQL Server 2008 Express is installed on the machine as well as SQL Server CE 3.5 SP1. In the machine.config there is an entry: <add name="Microsoft SQL Server Compact Data Provider" invariant="System.Data.SqlServerCe.3.5" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/></DbProviderFactories> The appl...

how to set right time in scheule
How shall i do a formula to make a time sheulde. (for my work hour.) I have made cell 1 when i start the job , cell 2 when i stop the work and cell 3 forpause/food time and cell 4 the sum hour my working day. It work fine when i work on the ordnary day but when i work late or night exel dont get right sum :-/ -- Henrik Go to the following URL and scroll down the page until you find Time functions. I think you'll find your solution here: http://www.cpearson.com/excel/datearith.htm -- Kevin Backmann "Henrik Björkner" wrote: > How shall i do a formula to make a ti...

when is the time right to create a sub to do something?
hello i have a project where i have lots of fields that filter things on the forms. i have the typical apply filter button, but i have also been using a key catch for the enter key. when a user updates the filtering field and hits enter i cause it to requery the sub query that the filtering field creates a filter for. on some forms the amount of times i put in the code: If KeyAscii = vbKeyReturn Then Me.mySubform.Requery End If is ridiculous. so the question is should i make a routine for the form(s) that calls the little bit of code? it seems so repeatative to put th...

Suddenly Black&White icons on mouse right-click, why?
Subject says it all, can someone comment? Not terribly important (I can liev with black & white icons), but annoyance indeed and only started recently. PLEASE PLEASE note: It does NOT affect WindowsExplorer/Filemanager icons for folders, files, etc. Those have been and are fine 100%. Just the mouse right-click, now shows weird black&white icons, icons themselves are OK, colors are not. Difficult to see quickly what to click as I used to know by color what is what..... For example right-click on empty space in Windows Explorer and select "new text Document&...

Change column name??????
:rolleyes: *Can somebody help my out?????? I would like to know how to change the column name (A, B, C etc) into a different name...* --- Message posted from http://www.ExcelForum.com/ Good question BH 79 , I also would really like to know this and can't find anything about it :confused:. But I have seen docs in which the columns have names instead of the usual a,b,c-labels... Hope someone knows the answer... --- Message posted from http://www.ExcelForum.com/ "BH79 >" <<BH79.zrnhc@excelforum-nospam.com> wrote in message news:BH79.zrnhc@excelforum-nospam.com......

Named Instance of SQL
Hi there, We have inherited a client who installed MSCRM but on a named SQL Instance. I know this is not supported, and so far have found that you are unable to go offline from SFO. Does anyone know if there are any other implications. Thanks in advance Sam what happens when you go offline? "Samantha" <anonymous@discussions.microsoft.com> wrote in message news:966901c43385$cfe3db60$a001280a@phx.gbl... > Hi there, > > We have inherited a client who installed MSCRM but on a > named SQL Instance. I know this is not supported, and so > far have found that y...

VLookup Returning N/A
Hello: I having a problem with a VLookup formula. My main spreadsheet is a download from a mainframe and the lookup table (on a separate spreadsheet) is inputted directly into Excel. The lookup value is a PO number. All the VLookup Formulas are returning N/A. The weird thing is that if I manually type in the PO Number in the main spreadsheet, then the formulas work. Or if I copy the PO Number from the main spreadsheet into the lookup table, then the formulas work. Obviously the PO numbers in the main spreadsheet and the PO numbers in the lookup table aren't matching, but I can't f...

How to look up a value in a list and return multiple corresponding
I followed the instructions on the excel help page of how look up a value in a list and return multiple corresponding values but somethings not right. I have tried this numerous times in my excel spreadsheet and it does not return any values let alone numerous values. No values will appear unless I go into the insert menu and click function, but then here it will only give me the smallest value, even when there is more than one. Please help asap! Thank you. How about giving us some details? -- Biff Microsoft Excel MVP "123456789" <123456789@discussions.m...

Round bullet with a right arrow symbol
Hi, For some reason all of my documents that had a normal single bullet, such as a round black bullet, now has the bullet with a black right arrow attached. I can't get rid of the black arrow. I don't know why this suddenly popped up. I just want a bullet. I have tried erasing the bullet and reappling. I've tried "customize" and it shows a regular bullet but still inserts with a black arrow. It started a few days ago. What you're seeing is probably just the character for the tab that normally follows a bullet. Hide the non-printing characters an...

Outlook 2003: Copy Command Missing on Right-Click
In Outlook 2003, when I right click on an email message, only the move command is available; the copy command is missing! The only way I can copy an email message to another folder is to drag it with the right mouse button and then it comes up as an option. Is there a fix to this bug? Thanks... ...

reset contact names display in outlook reading pane
Over time, due to inefficiencies on my part as well some company changes have made the name display in the reading pane and other places quite messy. For example, my company renamed a division so people who were once Smith, John (north division) are now Smith, John (professional division) and those I accidentally accepted from incoming emails as Mary Jones for the display, I would prefer to display as Jones, Mary. I may have compounded the problem because I do use the contacts as my default due to the fact I have many external customers and our company is extremely la...

Column A is Town, Column B is names. How can Excel add & tell how.
Column A is Town, Column B is names, Column C is how many in each household. How can Excel add & tell me how many Smiths in each town and, if possible, how many Smith families with 2 people in each town. Then I want to chart the info. How many of each family name by town. Read Help for Pivot Table -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Cindy" <Cindy@discussions.microsoft.com> wrote in message news:35575F1E-F67D-47C9-AD18-D1658405A5E9@microsoft.com... > Column A is Town, Column B is names, Column C is how many in each > hous...

Purcahse Return
How can i receive the amount from vendor against purchase return.i did the full payment against purchase On Aug 22, 9:35 am, qurat <qu...@discussions.microsoft.com> wrote: > How can i receive the amount from vendor against purchase return.i did the > full payment against purchase Hi Qurat, To receive payment against purchase return, there is a workaround available 1. Enter return using "Retuns Transaction Entry" window & post it 2. Enter an "Misc Charges" Entry in "payable transaction entry" window, and apply it against the return. 3. Enter ...

His Name is "LORD Almighty" not god! Look for urself
Open our eyes and ears Jesus Christ is Lord ...

return all entries where name contains "LEN"
I have a table: (Dummy Scenario) -ID -Name -Join_Date -Remark ------------------------------------------------------------------------------------- I'd like to: Retrieve all entries where the name contains "LEN" , so names such as "ALLEN", "LENNY", "LENUS" etc.. should be returned. (In SQL, we write --> where the name LIKE "*LEN*", ) ------------------------------------------------------------------------------------- My question: 1) how can i do it in a form ? 2) Is that possible to return all results into a "DataG...

DoModal() returning -1
I have a class derived from CDialog that I call DoModal() on. Up until recently this was working fine. Now it seems it always returns -1 without appearing (It never hits OnInitDialog()). How can I diagnose this problem? Thanks, Drew Did you remove a control form the dialog template? Or did the ID of the dialog template change. Step into the DoModal method and see where it breaks. AliR. "Drew Myers" <drew.nospam.myers@esrd.com> wrote in message news:eoB8lH7iFHA.2484@TK2MSFTNGP15.phx.gbl... > I have a class derived from CDialog that I call DoModal() on. Up until ...

CRM Cluster Nodes cannot reference virtual name
We have an installation with CRM two clustered nodes using Windows 2003 NLB Nodes: CRM01, CRM02 Virtual Name: CRM Apart from standard CRM implementation, we use CRM assemblies, both for workflows but also for pre/post-callouts Users PC's can access the system using http://crm:5000 (the virtual name) and NLB correctly balances the load between the two nodes The problems start when trying to access CRM from the web server nodes. Using http://crm01:5000 works without requiring username/password (integrated authentication) Using http://crm02:5000 works as above Using http://crm:5000 works giv...

Public Function to Return ColorIndex
Hello, I made (copied) a function that returns the Index of the background of a cell. I saved it in a module in my Personal.xls workbook. Having done that, I thought I=B4d be able to use it like: =3DGetColorIndex(B5) But... I have to use it like: =3DPersonal.xls!GetColorIndex(B5) Is there a way to achieve the first option, simply =3DGetColorIndex(B5) without the Personal.xls! part before the function? thanks, Fries Hi Fries, One way: In the VBE set a reference to your Personal.xls workbook: Tools | References | Scroll to and tick the Personal.xls project You can then use Perso...

MSADC Error: Directory returned error:[0x51] Server Down
Hey guys, I have been battling with my Exchange 2003 and Exch 5.5 servers issues for a while now. Im starting to get very affraid that something major will happen shortly. I am creating users on my Exchange 2003 server which has ADC installed. This is NOT a DC. I have been having problems where users accounts and/or mailboxes are not getting replicated thru my AD and Exch servers. This is one main error that I am consistently getting on the Exchange 2003 server. Event Type: Error Event Source: MSADC Event Category: LDAP Operations Event ID: 8026 Event ID: 8026 Date: 4/25/2...

Using a named range in a chart
Can a named range be used in a chart for one of the series? I have tried this, and it doesn't seem to work. For instance, i have a name _Date_rng defined (using Insert...Name...Define) as "data!$A$2:$A$60". In a chart (XY scatterplot), for one series, i define the X values as _Date_rng. Excel formats this as ={"_Date_rng"}. However, the chart is now blank, it draws with the axes, but no data is plotted. But, when i put the range directly in the chart "Source Data...Series" tab as "data!$A$2:$A$60", it plots the data correctly. So, is it po...

User Rights / Permissions
Hi All We have a call out that based on business logic closes the appointment as completed, now this works fine for the admin user but when we use one of our sales users the call out throws an error saying user does not have the rights / permissions. Can anyone tell me which user permission controls the ability to close down an appointment as eith cancelled or completed. Our other option is to force the call out to user a different user with different permission, but I'd like to get this resolved Cheers Aly ! -- Alistair 2B.net http://www.crmdeployment.co.uk Hi, If user can ...