Hello,
I would really appreciate if anyone could assist or point me in right area.
I have two spreadsheets, Spreadsheet A and Spreadsheet B
Spreadsheet A contains:
InternalID
First Name
Last Name
Spreadsheet B contains:
ExternalID
InternalID (identical to Spreadsheet A Internal ID)
Request:
I need to add the External ID to Spreadsheet A.
Any ideas?
Last edited by shade45; 07-20-2012 at 06:23 PM.
A Vlookup..or INDEX,MATCH function would do it.
=vlookup(A1,SheetB!A:B,2,false) if Internal ID is in ColA and Ext ID in ColB
else, if its the other way around
=INDEX(SheetB!A:B,MATCH(A1,SheetB!B:B,0),1)
Life's a spreadsheet, Excel!
Say thanks, Click *
I have more fields in each Sheet.
Sheet A:
ID (different than the Internal ID)
Employee Number
First Name
Last Name
Title
Facility Code
Internal ID
External ID (area where I want to populate with Sheet B)
Sheet B
External ID
Internal ID
In that case, Column H on SheetA is where you want these...
=INDEX(SheetB!A:B,MATCH(G1,SheetB!B:B,0),1) as you are looking up values in Column G
Hi Ace_XL,
I have pasted =INDEX(SheetB!A:B,MATCH(G1,SheetB!B:B,0),1) into Column H on SheetA and then it prompted me to choose the location of SheetB which I did.
Once I did that I get #N/A
Are these separate workbooks, or just different tabs on the same workbook? Kindly upload a sample to better understand..
Two seperate workbooks.
I have attached a sample for you.
Thanks so much!
Open both workbooks...
In Cell H2 of aa.xls use
=IFERROR(INDEX([BB.xlsx]Sheet1!$A:$A,MATCH(G2,[BB.xlsx]Sheet1!$B:$B,0),1),"") and drag down. Also change the format of all cells in Column G to number or genral format.
For some reason I cannot get this to work....
=IFERROR(INDEX([Prox.xlsx]Sheet1!$A:$A,MATCH(G2,[Prox.xlsx]Sheet1!$B:$B,0),1),"")
I open my employees.xlsx worksheet which is identical to the AA.xlsx worksheet that I provided you however this one has real data.
I went to H2 and pasted : =IFERROR(INDEX([Prox.xlsx]Sheet1!$A:$A,MATCH(G2,[Prox.xlsx]Sheet1!$B:$B,0),1),"") where Prox.xlsx is the worksheet (similar to BB.xlsx) however with real data.
I then changed the H column to Number formation and dragged the cell down the list however the H Column did not pupulate.. no errors this time.
Ok I see why it is not working...
I have to click on each value in Column G and click on 'Convert to Number' once I do that the H Column populates.
I tried to format the column to Numbers but this did not work.. it seems I need to click on each cell in the G Column and click on 'Convert to Number'
Did you get this resolved? If yes, please mark the thread as solved
To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save
Thanks for all your help! Problem Solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks