+ Reply to Thread
Results 1 to 8 of 8

Match cell value and return another column value

  1. #1
    Registered User
    Join Date
    08-23-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    57

    Question Match cell value and return another column value

    Hey guys,

    I have roughly 30 sheets of data and I am looking to match values from one sheet to a range in another sheet and then return the value in a separate column of the same row.

    Example:
    • On Sheet1 I have a range of names (A2:A2000) and range of associated email addresses (AF2:AF2000)
    • On Sheet5 I have a list of names (starting with A2)
    • I want to add a formula in a column that takes the name in Sheet5!A2, finds the match in Sheet1!A2:A2000 and then returns the associated email address from Sheet1!AF2:AF2000

    I have tried a few different formulas, however, I can't seem to make one that fits the needs:

    Currently, Sheet5!A2 is matched to Sheet1!A756, so I need to return the value of Sheet1!AF756
    A macro does what you tell it, not what you want.

  2. #2
    Registered User
    Join Date
    04-17-2012
    Location
    No, Thanks
    MS-Off Ver
    MS 2010
    Posts
    14

    Re: Match cell value and return another column value

    Wouldn't a VLOOKUP work for this?

    =VLOOKUP(Sheet5!A:A,Sheet1!A:AF,28,0)

  3. #3
    Registered User
    Join Date
    08-23-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: Match cell value and return another column value

    I have tried a few very similar and just tried yours as well; however, I receive a #REF! error


    ***edit***
    Actually, I am returning all "0" values
    Last edited by thinkspac; 11-21-2012 at 11:40 AM. Reason: additional info

  4. #4
    Forum Contributor
    Join Date
    11-05-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Match cell value and return another column value

    I think the reason you are getting the error is because the lists of names are not perfectly matched. It may be caused due to extra spaces.
    Could you upload a sample, so we could check for ourselves?
    Taming the Excel dragon... www.TheExcelphile.com

  5. #5
    Registered User
    Join Date
    04-17-2012
    Location
    No, Thanks
    MS-Off Ver
    MS 2010
    Posts
    14

    Re: Match cell value and return another column value

    I usually have a column of input and a second column of =TRIM(input) next to it. Then I do my lookups off of the trimmed column.

    I work with part numbers, not names, but they still have tricky leading and trailing extra spaces - maybe that's what your issue is too.

  6. #6
    Registered User
    Join Date
    08-23-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: Match cell value and return another column value

    There are no leading or trailing spaces as the additional sheets are populated from the original sheet (copy/paste) via a macro.

    I cannot upload my actual document but I have provided an example sheet for testing (with notes).
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-17-2012
    Location
    No, Thanks
    MS-Off Ver
    MS 2010
    Posts
    14

    Re: Match cell value and return another column value

    thinkspac,

    Entering a normal VLOOKUP into Sheet2!C1 worked fine:

    =VLOOKUP(Sheet2!A:A,Sheet1!A:C,3,0)

    Not sure what the problem is without seeing an example of your actual data set.

  8. #8
    Registered User
    Join Date
    08-23-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    57

    Thumbs up Re: Match cell value and return another column value

    Let me see if i can't diagnose the deeper issue, as you are right, the standard VLOOKUP does work in this example.

    I will go ahead and mark this as a resolved.

    Thanks for the assistance!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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