+ Reply to Thread
Results 1 to 8 of 8

Formula to Match Data between Sheets and return a Value

  1. 07-10-2016, 11:31 PM #1
    hammer2
    hammer2 is offline
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Formula to Match Data between Sheets and return a Value

    Hi Folks
    Easy one (except for Dummies like myself).

    Formula to start in Z5 and drag down.

    IF Y5= YES then lookup A5 for a Match in Sheet2 Col A and if match found to return the corresponding value from Col B in Sheet2 to Z5.

    IF Y5 is showing NO then Z5 to return ""

    Expected returns shown in Col Z

    Sample attached
    Cheers
    hammer
    Attached Files Attached Files
    If you have found solving my problem/s to be an interesting and educational exercise then how about Repping me up?
    No idea why I need Rep, other than feeling left out....
    Reply With QuoteRegister To Reply
  2. 07-10-2016, 11:38 PM #2
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2404
    Posts
    13,406

    Re: Formula to Match Data between Sheets and return a Value

    Try
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Dave
    Reply With QuoteRegister To Reply
  3. 07-10-2016, 11:41 PM #3
    rcm
    rcm is offline
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Formula to Match Data between Sheets and return a Value

    introduce this formula in Z5 and copy it down...
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 07-10-2016, 11:45 PM #4
    dmg2016
    dmg2016 is offline
    Registered User
    Join Date
    07-08-2016
    Location
    USA
    MS-Off Ver
    Office 365, Office 2016 for PC & Mac, Office 2013, Office 2011 Mac
    Posts
    80

    Re: Formula to Match Data between Sheets and return a Value

    I used trim because there is a space after Bill on one of the sheets.
    Please Login or Register  to view this content.
    Otherwise this would work.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 07-10-2016, 11:48 PM #5
    hammer2
    hammer2 is offline
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Re: Formula to Match Data between Sheets and return a Value

    Thanks Folks
    I had tried VLOOKUP but as I cannot sort Col A in Sheet2 I thought I may run into problems by using VLOOKUP.

    Col A is populated by a program and I cannot alter how it is imported.

    Both solutions above seem to be working though.
    Reply With QuoteRegister To Reply
  6. 07-10-2016, 11:58 PM #6
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2404
    Posts
    13,406

    Re: Formula to Match Data between Sheets and return a Value

    hammer2,

    Sorting shouldn't be an issue since the formulas above are doing exact matches. It you were doing approximate matches (TRUE) then that would be a different story. You would need sorted column A.
    Reply With QuoteRegister To Reply
  7. 07-11-2016, 12:26 AM #7
    hammer2
    hammer2 is offline
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Re: Formula to Match Data between Sheets and return a Value

    Many thanks again all-
    Much appreciated.
    Reply With QuoteRegister To Reply
  8. 07-12-2016, 09:15 PM #8
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2404
    Posts
    13,406

    Re: Formula to Match Data between Sheets and return a Value

    You're welcome. Thanks for the feedback.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] a formula to match and return data
    By josh101287 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-26-2016, 07:39 AM
  2. Need an Excel formula that can return values from multiple sheets via INDEX and MATCH
    By aSquared in forum Excel General
    Replies: 2
    Last Post: 05-27-2015, 12:45 AM
  3. [SOLVED] Help on a Match & Return Data formula
    By alan_stephen75@ in forum Excel General
    Replies: 18
    Last Post: 01-28-2015, 08:05 AM
  4. Match 1st and 2nd columns in 2 sheets and return 3rd,4th and 5th column if match.
    By shaXad in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-16-2013, 04:02 AM
  5. [SOLVED] Two Excel sheets trying to match text data and return the match from the second column!
    By bankcott in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-02-2013, 02:17 PM
  6. [SOLVED] Formula to match data ( multi column) in different sheets and return matching values
    By kangyao in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-17-2013, 08:49 PM
  7. [SOLVED] Formula to match data in different sheets ( multi column) and return matching values
    By kangyao in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-11-2013, 07:38 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1