+ Reply to Thread
Results 1 to 9 of 9

Trying to lookup a value in a different worksheet and get Y if found, or N if not.

  1. #1
    Registered User
    Join Date
    10-13-2014
    Location
    Tampa, Florida
    MS-Off Ver
    2003
    Posts
    5

    Trying to lookup a value in a different worksheet and get Y if found, or N if not.

    For each row in my worksheet I am referencing a column (the value represents an account id) in a formula, and I am trying to find a matching row to the account ids in Column A of another worksheet called Benefit. If the account id is found in the Benefit worksheet, I want to return a value of Y. If not I want it to return a value of N. Based on what I am reading I think I need to use the IF function.

    Here’s an example of what I have coded on the 1st rows in cell in a blank cell where I want my value populated. C1 contains the account id in the current worksheet. The Benefit worksheet has a list of account ids in column A from rows 4 thru 49. Some of he account ids on the first worksheet may be found in the Benefit worksheet but some may not.
    I am trying this formula but it returns an N value in all cases (whether found or not)
    =IF(C1=Benefit!$A$4:$A$49,"Y","N")

    If I point specifically to the cell that I know has the value in the Benefit worksheet, then I get my expected Y value.
    =IF(C1=Benefit!$A$18,"Y","N")

    Of course I want Excel to search for it and tell me if it’s found or not, versus me pointing to the cell. How can I accomplish this?

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Trying to lookup a value in a different worksheet and get Y if found, or N if not.

    One way:
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Trying to lookup a value in a different worksheet and get Y if found, or N if not.

    =IF(ISNUMBER(SEARCH(C1,Benefit!$A$4:$A$49)),"Y","N")

    and copy down
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  4. #4
    Registered User
    Join Date
    10-13-2014
    Location
    Tampa, Florida
    MS-Off Ver
    2003
    Posts
    5

    Re: Trying to lookup a value in a different worksheet and get Y if found, or N if not.

    YES! Thank you - that works! Now, so that I understand this. Does this work because when the COUNTIF finds any match it will return a 1 (or more), then that will result in a "true" results, and if the COUNTIF returns a 0 then that will result in a "false" result?

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Trying to lookup a value in a different worksheet and get Y if found, or N if not.

    You're welcome. Your understanding is correct. A positive value will be treated as TRUE, a zero value as FALSE.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  6. #6
    Registered User
    Join Date
    10-13-2014
    Location
    Tampa, Florida
    MS-Off Ver
    2003
    Posts
    5

    Re: Trying to lookup a value in a different worksheet and get Y if found, or N if not.

    Thanks also Daffodill11. I was able to get the COUNTIF to work from TMS, but will keep this stored for possible future use. Can you explain what the difference is between them to me?

  7. #7
    Registered User
    Join Date
    10-13-2014
    Location
    Tampa, Florida
    MS-Off Ver
    2003
    Posts
    5

    Re: Trying to lookup a value in a different worksheet and get Y if found, or N if not.

    Very fast and accurate response - thank you!

  8. #8
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Trying to lookup a value in a different worksheet and get Y if found, or N if not.

    Search(search for what, where) returns the character number where it begins. Therefore, SEARCH("some","awesome") = 4.

    IF relies on TRUE/FALSE inputs, so I encompass the search with ISNUMBER() which then tests for a number. Interestingly enough, it also evaluates errors as False.

  9. #9
    Registered User
    Join Date
    10-13-2014
    Location
    Tampa, Florida
    MS-Off Ver
    2003
    Posts
    5

    Re: Trying to lookup a value in a different worksheet and get Y if found, or N if not.

    Ok, got it! Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] need lookup to display values as they are found
    By inq80 in forum Excel General
    Replies: 19
    Last Post: 09-07-2014, 12:59 PM
  2. [SOLVED] Lookup value and get minimum value of date in that found row
    By martinhansen in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-17-2014, 04:05 AM
  3. Copy data from worksheet 2 to worksheet 1 if match found
    By TSSRob in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-18-2011, 07:54 PM
  4. [SOLVED] can lookup return err if no match found
    By Kim Greenlaw in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-11-2006, 01:45 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1