+ Reply to Thread
Results 1 to 5 of 5

Compare Two Cells on Sheet2 to Two Columns on Sheet 1 and Bring Back all Results

  1. #1
    Registered User
    Join Date
    08-02-2012
    Location
    Madison, WI
    MS-Off Ver
    Excel 2007
    Posts
    4

    Compare Two Cells on Sheet2 to Two Columns on Sheet 1 and Bring Back all Results

    Hello. This is my first time posting to this forum, though I use this resource frequently! Thank you for all your hard work!

    I have attached a very simple example of the document in question to illustrate my needs. This document will be used to project staffing requirements for company projects. Sheet 1 is the input tab. A staffer will input all the people that will work on the project and the employee’s internal job code and client job code will return with the use of VLOOKUPs.

    Sheet 2 summarizes all of this information into a template by unique combinations of different job codes. The unfortunate part of our process is that our company’s internal job code does not match that of our client's for the same work. Ex. Our job code 1234 can be either an Analyst or an Accountant while an Analyst can be codes 1234, 1235, or 1236 internally.

    What I need is a formula for Sheet 2, Column A that will look at the unique combination of job codes between our company and the client and bring back the names of all the employees into one cell.

    I got as far as utilizing a lookup_concat formula (an example is in Sheet2, Cell A2). However, this formula works by referencing only one cell; I need it to reference two. Is there a modification that can be done?

    Thank you so much for your help!
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Compare Two Cells on Sheet2 to Two Columns on Sheet 1 and Bring Back all Results

    hi Betty22, welcome to the forum. perhaps you can try copying this & paste into the formula bar:
    =INDEX(Sheet1!A2:A11,MATCH(B2&C2,Sheet1!B2:B11&Sheet1!C2:C11,0))

    then press CTRL + SHIFT + ENTER

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    08-02-2012
    Location
    Madison, WI
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Compare Two Cells on Sheet2 to Two Columns on Sheet 1 and Bring Back all Results

    Thank you for your response. We're close! This brings back the first response, but I do need multiple responses to fill in to the one cell. I think we're on the right path!

    In the case of my little example sheet, this brings back Robert to cell A2, but I would like to see "Robert, Billy". Both are 1234 and Accountants. Any thoughts as to how I can get them both into the same cell? Can we mash a concate and index formula together? I had never used concate before I tried it here. It was pretty slick until I realized there is no 1:1 match between us and the client (ah, the business world).

    There is a function saved on my example that allows the concate to work, maybe that needs to be modified?

  4. #4
    Registered User
    Join Date
    08-02-2012
    Location
    Madison, WI
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Compare Two Cells on Sheet2 to Two Columns on Sheet 1 and Bring Back all Results

    Bump no response

  5. #5
    Registered User
    Join Date
    08-02-2012
    Location
    Madison, WI
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Compare Two Cells on Sheet2 to Two Columns on Sheet 1 and Bring Back all Results

    I didn't have much luck in the way of responses, so I'm getting clever! I'm going to create a new column that combines the contents of the job codes with a &"."&. This will create unique identifiers, and I'll just hide the column. Thank you everyone!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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