+ Reply to Thread
Results 1 to 2 of 2

Taking correspondence between 2 columns to specify another Excel sheet

  1. #1
    Registered User
    Join Date
    12-07-2020
    Location
    Netherlands
    MS-Off Ver
    MS Excel for Mac v16.43
    Posts
    1

    Taking correspondence between 2 columns to specify another Excel sheet

    Hi everyone

    I have a lookup file - see sample 1 - which shows the correspondence between ID codes of universities in Europe (column B) and the EU region code where they are located (column E). I have another file which shows the yearly international student mobility to and from these universities, identified with the same ID codes (columns A and K) - see sample 2. The problem is that in this second file the info about the region is missing. I cannot just copy-paste it because in file no. 2 every individual student is recorded, meaning that each university has hundreds of entries. The total is over 250,000 (all the students moving around Europe in a given year). So, what I need is a way to tell Excel to find in file no. 2 the ID codes of the universities, and then retrieve from file no. 1 the corresponding region code, automatically filling the free columns next to A and K with that information.

    Hope this is clear, I would be grateful for some guidance on how to achieve this.
    Thanks a lot!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    08-26-2016
    Location
    UK
    MS-Off Ver
    O365
    Posts
    260

    Re: Taking correspondence between 2 columns to specify another Excel sheet

    See formulae in column B (and L):
    =INDEX('C:\Local\Temp\[Excel_sample1.xlsx]Sheet1'!$E$2:$E$20,MATCH(A2,'C:\Local\Temp\[Excel_sample1.xlsx]Sheet1'!B$2:B$20,0))
    This includes the local filepath because that's how Excel saves formulae when th data comes from another file. You need to do one of two things:
    either amend the formula so that the file paths are correct, or, easier, have both files open when the formula can be in the shorter form:
    =INDEX([Excel_sample1.xlsx]Sheet1!$E$2:$E$20,MATCH(A2,[Excel_sample1.xlsx]Sheet1!B$2:B$20,0))
    (you still have to get the file name right both times).

    Currently they're all showing errors because (a) the file they're looking for isn't there and (b) there's no matching data at all in the files you supplied.

    To prevent the continued reliance on the presence of the other file, you should copy/paste-special |Values in situ to convert the results of the formulae to plain values.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy specified text file and paste to correspondence excel sheets
    By nicks1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-10-2018, 11:35 AM
  2. Replies: 3
    Last Post: 10-06-2018, 12:52 AM
  3. Taking data from different excel sheet in one Master sheet
    By Dost.Rehman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-24-2017, 02:45 AM
  4. Cleaning up excel sheet taking too long
    By slamdunka in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-25-2015, 02:32 AM
  5. Simple Stock Taking Excel sheet (need help on forumlas etc)
    By Mr.West in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-09-2014, 05:14 PM
  6. Taking path name from another excel sheet
    By missf in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-06-2011, 04:46 AM
  7. Taking data from excel sheet into userform
    By darklide in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-13-2010, 03:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1