+ Reply to Thread
Results 1 to 5 of 5

Marrying two spreadsheets based on one column with same info

  1. #1
    Registered User
    Join Date
    11-30-2016
    Location
    Baltimore
    MS-Off Ver
    varies
    Posts
    2

    Marrying two spreadsheets based on one column with same info

    Hi Everyone! I have a question. I work in an HR office and often have the problem where I have two different spreadsheets with different info that I want on one spreadsheet. At least one column is almost always the same. For example:

    spreadsheet 1

    EmployeeName......LocationName
    ---------------------------------
    Harry Potter.........Hogwarts

    Spreadsheet 2

    LocationName......LocationNumber
    ---------------------------------
    Hogwarts............03372

    In the example, I'd like to be able to tell excel to pull the LocationNumber cell contents into spreadsheet 1, based on the mutual LocationName column. So if Harry Potter is located at Hogwarts, it would look on spreadsheet 2 for the Hogwarts row and pull in the 03372 LocationNumber from the column next to it to a new 3rd column on spreadsheet 1.

    I hope I explained that right. Does anyone know of a formula I could use that would help me accomplish this? I feel like I might be able to do it with some sort of 'if' formula, but I don't have enough of the syntax to figure that out...

    Any ideas?

    Thanks!

    Oh, and I should probably mention that I don't have enough admin access to my computer to install any add-ons or use online software.

    Unfortunately I'm stuck with Excel 2013.
    Last edited by imjemma; 12-06-2017 at 05:02 PM.

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Marrying two spreadsheets based on one column with same info

    I assume your MS Off. version: varies contain 2016/365
    so use PowerQuery, this is built-in feature

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,991

    Re: Marrying two spreadsheets based on one column with same info

    try

    in C2 on Sheet1

    =VLOOKUP($B2,Sheet2!$A$2:$B$100,2,0)


    B2 =Hogwarts (Sheet1)

    Data in Sheet2 starts in row 2 columns A & B

  4. #4
    Valued Forum Contributor
    Join Date
    12-01-2016
    Location
    Planet Earth
    MS-Off Ver
    95 - 2016
    Posts
    343

    Re: Marrying two spreadsheets based on one column with same info

    Assuming the following
    Sheet1 contains:
    - EmployeeName (Column A and at Row 1)
    - LocationName (Column B and at Row 1)

    Sheet2 contains
    - EmployeeID (Column A and at Row 1)
    - LocationName (Column B and at Row 1)
    - LocationNumber (Column C and at Row 1)

    Calculation for Sheet1 C2 would be: =INDEX(Sheet2!$C$2:$C$4,MATCH($B2,Sheet1!$B$2:$B$4,0))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Drag down to last entry

    See attachment
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,991

    Re: Marrying two spreadsheets based on one column with same info

    You do not need to enter as an array formula.

+ 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. [SOLVED] Extract selected column data based on column header info
    By ajay6985 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-09-2017, 02:07 AM
  2. [SOLVED] Calculating info in a column based on a "greater than" date & info in another column
    By fpm2002 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-06-2017, 12:58 AM
  3. Need Advice
    By AltaAbrie in forum Excel General
    Replies: 1
    Last Post: 10-17-2014, 08:09 PM
  4. Replies: 4
    Last Post: 10-02-2013, 03:00 PM
  5. Marrying information
    By gblack6181 in forum Excel General
    Replies: 1
    Last Post: 07-23-2012, 03:39 PM
  6. Join Cells in One Column Based on Info in 2nd Column
    By fedorzarkhin in forum Excel General
    Replies: 8
    Last Post: 04-08-2012, 04:18 PM
  7. Map two excel spreadsheets based on a column
    By raj2007 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-19-2008, 01:49 PM

Tags for this Thread

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