+ Reply to Thread
Results 1 to 7 of 7

compare 2 columns return value from 3rd column

  1. 02-20-2024, 11:09 AM #1
    dadadavid
    dadadavid is offline
    Registered User
    Join Date
    01-08-2018
    Location
    jerusalem
    MS-Off Ver
    2016
    Posts
    25

    compare 2 columns return value from 3rd column

    lookup.JPG

    Hi, I want to compare column A (contains all IDs) to column B (contains index of IDs) and place the value from column C (Plant Source) in column D.
    So from the example posted cell D11 should have value "B"
    What would be the formula I need to enter in D2 ?
    Any help much appreciated.
    Thanks
    Reply With QuoteRegister To Reply
  2. 02-20-2024, 11:38 AM #2
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: compare 2 columns return value from 3rd column

    this would be one way... =VLOOKUP(A2,A:C,3,FALSE)
    change the commas to semicolons if your locale needs that.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci
    Reply With QuoteRegister To Reply
  3. 02-20-2024, 12:41 PM #3
    dadadavid
    dadadavid is offline
    Registered User
    Join Date
    01-08-2018
    Location
    jerusalem
    MS-Off Ver
    2016
    Posts
    25

    Re: compare 2 columns return value from 3rd column

    Thanks for this Sam.
    I probably didn't explain myself very well. The formula you suggest is returning "B" to all column D, what I need is for D2:D10 and D12:D22 to be blank 'cause there's no matching value in col B (Plant Index).
    Thanks very much for your help.
    Reply With QuoteRegister To Reply
  4. 02-20-2024, 08:22 PM #4
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: compare 2 columns return value from 3rd column

    is this what you are looking for... =IFERROR(VLOOKUP(A2,$B$2:$C$24,2,FALSE),"")
    it will leave blanks from D2 through D10, then return B then blank again until D23 and D24.
    Reply With QuoteRegister To Reply
  5. 02-20-2024, 08:48 PM #5
    dadadavid
    dadadavid is offline
    Registered User
    Join Date
    01-08-2018
    Location
    jerusalem
    MS-Off Ver
    2016
    Posts
    25

    Re: compare 2 columns return value from 3rd column

    Hi Sam, going slowly insane - yes this is exactly the result I want, don't know what I'm doing wrong but when I copy/paste your formula I get all blank results. Thought maybe col-_index_num should be 3 instead of 2 but no change in results. Any help in showing me the error of my ways much appreciated.
    Reply With QuoteRegister To Reply
  6. 02-20-2024, 09:38 PM #6
    dadadavid
    dadadavid is offline
    Registered User
    Join Date
    01-08-2018
    Location
    jerusalem
    MS-Off Ver
    2016
    Posts
    25

    Re: compare 2 columns return value from 3rd column

    OK Sam think I've found the problem, after running TRIM on columns A and B, I tried your formula again and it worked perfectly.
    Thanks very much for your help
    Reply With QuoteRegister To Reply
  7. 02-21-2024, 12:17 AM #7
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: compare 2 columns return value from 3rd column

    Glad it is resolved and if I was helpful.
    Needing to add TRIM to the data likely would've been one of the things that could've been discovered if you had posted a sample workbook as I had to work from my mockup.
    That is one of the many reasons why we ask that people post a sample workbook, apart from having us try to replicate your data which can be cumbersome.
    Please don't forget to mark the post as solved and if you are so inclined clicking on * Add reputation is always appreciated as this is a voluntary help site.
    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. Compare columns in 2 sheets and then return a value in another column
    By makaveeti in forum Excel General
    Replies: 4
    Last Post: 08-28-2020, 06:46 PM
  2. [SOLVED] Compare two columns and return a value in a third column
    By Mr. H in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-09-2018, 01:38 AM
  3. Compare multiple columns (column A,column B,Column C) and return value (Column D)
    By john008 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-21-2015, 08:24 AM
  4. compare two columns and return match from third column
    By ozza89 in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 03-08-2014, 05:24 AM
  5. [SOLVED] Compare 2 columns with another 2 columns as return value from 3rd column
    By DHFE in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-04-2013, 07:54 AM
  6. Need to compare two sets of columns and then automatically return value in 5th column
    By sandy3333 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-25-2013, 03:20 PM
  7. Compare values in 2 columns, return Y/N in a third column
    By creed1101 in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 07-08-2009, 01:29 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