+ Reply to Thread
Results 1 to 12 of 12

Simple formula to match column A, sheet1, with column A, sheet2, return text answer

Hybrid View

  1. 11-14-2012, 09:48 AM #1
    Connie5761
    Connie5761 is offline
    Registered User
    Join Date
    03-04-2012
    Location
    Clinton Township, Michigan
    MS-Off Ver
    Excel 2003
    Posts
    28

    Question Simple formula to match column A, sheet1, with column A, sheet2, return text answer

    I have searched the forum for a couple of hours looking for what seems to me should be a simple formula but can't find anything similar, so I'm asking here. Please help me!

    I want to return "Active" or "Inactive" based on whether or not a number in column A on one sheet matches column A on another sheet. I have tried several things based on what you have helped me with in the past but it's not working. Here is what I am trying -

    IF(VLOOKUP(A:A,HOME!A:A,1,0),"Inactive","Active")

    where A:A is the column on the current sheet, and HOME is the other sheet. This returns an #N/A error in every cell, even when the numbers match. If I use a single quote around 'HOME!', it tells me there is an error in the formula.

    I'm sure this is a piece of cake for you gurus out there. I am trying to learn Excel on my own as I can't afford classes. I have gone to YouTube and learned alot but I'm not sure if I'm even using the right Excel formula. Should I be using MATCH or something else instead of VLOOKUP?

    Thank you in advance!
    Reply With QuoteRegister To Reply
  2. 11-14-2012, 09:56 AM #2
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Simple formula to match column A, sheet1, with column A, sheet2, return text answer

    Lookup value should be Single cell.

    Try the below In 2nd row
    Formula: copy to clipboard 
    =IF(A2="","",IF(ISNA(VLOOKUP(A2,HOME!A:A,1,0)),"Inactive","Active"))

    Drag it down

    OR

    Formula: copy to clipboard 
    =IF(A2="","",IF(COUNTIF(HOME!A:A,Sheet3!A2),"Active","Inactive"))

    Drag it down
    Last edited by :) Sixthsense :); 11-14-2012 at 09:56 AM. Reason: Correction in Formula Tag


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved

    If the suggestion helps you, then Click *below to Add Reputation
    Reply With QuoteRegister To Reply
  3. 11-14-2012, 11:36 AM #3
    Connie5761
    Connie5761 is offline
    Registered User
    Join Date
    03-04-2012
    Location
    Clinton Township, Michigan
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Simple formula to match column A, sheet1, with column A, sheet2, return text answer

    I only have two sheets - not sure why 'sheet3' is needed. I tried both - they give "Inactive" for all cells, even for those finding the number in HOME sheet column A.
    Reply With QuoteRegister To Reply
  4. 11-14-2012, 11:39 AM #4
    Vinoth Kanna
    Vinoth Kanna is offline
    Registered User
    Join Date
    11-14-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Simple formula to match column A, sheet1, with column A, sheet2, return text answer

    Can you pl provide me a sample dat
    Reply With QuoteRegister To Reply
  5. 11-14-2012, 11:59 AM #5
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Simple formula to match column A, sheet1, with column A, sheet2, return text answer

    Oops... Delete the Sheet3! From my above suggested formula. Oversight error.

    sent from mobile.
    Reply With QuoteRegister To Reply
  6. 11-14-2012, 12:24 PM #6
    Connie5761
    Connie5761 is offline
    Registered User
    Join Date
    03-04-2012
    Location
    Clinton Township, Michigan
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Simple formula to match column A, sheet1, with column A, sheet2, return text answer

    Here's the test sheet. Not sure what to replace "sheet3" with. Thank you for your help!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 11-14-2012, 12:29 PM #7
    kbkumar
    kbkumar is offline
    Forum Contributor
    Join Date
    09-27-2012
    Location
    London, England
    MS-Off Ver
    2003, 2010
    Posts
    344

    Re: Simple formula to match column A, sheet1, with column A, sheet2, return text answer

    The ID Column in sheet Q seems to be in text format, so you may want to convet that into Numbers while applying lookup formulas else they will show #N/A errors.
    If solved kindly remember to mark Thread as solved.
    Click the small star icon at the bottom left of my post if this was useful.
    Reply With QuoteRegister To Reply
  8. 11-14-2012, 12:38 PM #8
    Connie5761
    Connie5761 is offline
    Registered User
    Join Date
    03-04-2012
    Location
    Clinton Township, Michigan
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Simple formula to match column A, sheet1, with column A, sheet2, return text answer

    I converted both A columns on both sheeets to numbers but they still all show "inactive" regardless of whether or not the ID number is found on sheet HOME column A.
    Reply With QuoteRegister To Reply
  9. 11-14-2012, 12:52 PM #9
    kbkumar
    kbkumar is offline
    Forum Contributor
    Join Date
    09-27-2012
    Location
    London, England
    MS-Off Ver
    2003, 2010
    Posts
    344

    Re: Simple formula to match column A, sheet1, with column A, sheet2, return text answer

    I used the below in cell C2 of sheet Q and dragged down:

    Formula: copy to clipboard 
    =IF(ISERROR(VLOOKUP(A2,HOME!A:A,1,FALSE)),"Inactive","Active")


    Then converted column A of the same sheet into numbers (by selecting column A, pressting ALT + D + E + Finish

    This gives proper results in column C i.e. Active / Inactive.
    Reply With QuoteRegister To Reply
  10. 11-14-2012, 01:15 PM #10
    Vinoth Kanna
    Vinoth Kanna is offline
    Registered User
    Join Date
    11-14-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Simple formula to match column A, sheet1, with column A, sheet2, return text answer

    Converting the Column A in Sheet Q is working. Kindly select the entire data range and do as shown in the screenshot for converting to number.
    2012-11-14_2243.png
    Reply With QuoteRegister To Reply
  11. 11-14-2012, 01:24 PM #11
    Connie5761
    Connie5761 is offline
    Registered User
    Join Date
    03-04-2012
    Location
    Clinton Township, Michigan
    MS-Off Ver
    Excel 2003
    Posts
    28

    Cool Re: Simple formula to match column A, sheet1, with column A, sheet2, return text answer

    That did the trick! Thank you so much!
    Reply With QuoteRegister To Reply
  12. 11-14-2012, 02:56 PM #12
    Vinoth Kanna
    Vinoth Kanna is offline
    Registered User
    Join Date
    11-14-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Simple formula to match column A, sheet1, with column A, sheet2, return text answer

    Pl add the star if my post had helped you..
    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)

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