+ 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. #1
    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!

  2. #2
    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

  3. #3
    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.

  4. #4
    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

  5. #5
    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.

  6. #6
    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

  7. #7
    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.

  8. #8
    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.

  9. #9
    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.

  10. #10
    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

  11. #11
    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!

  12. #12
    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 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