Closed Thread
Results 1 to 7 of 7

lookup exact match returns TRUE or FALSE

  1. #1
    Registered User
    Join Date
    07-15-2010
    Location
    Vancouver
    MS-Off Ver
    Excel 2008
    Posts
    2

    lookup exact match returns TRUE or FALSE

    When text is entered in Column A, I want Column B to lookup the list in Column F and return either TRUE (it's in the list), or FALSE (it's not there). And it has to be an exact text match. The other formulas I have tried return TRUE even if it's just a character match.
    Last edited by quade_1; 07-15-2010 at 10:21 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: lookup exact match returns TRUE or FALSE

    Perhaps

    =IF(ISNUMBER(MATCH(A1,F:F,0)),EXACT(A1,INDEX(F:F,MATCH(A1,F:F,0))),FALSE)

    where A1 is being matched to column F
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: lookup exact match returns TRUE or FALSE

    =SUMPRODUCT(--EXACT($F$1:$F$100,A2))>0
    i think the match will fail if any above above it are the same but a different case
    Last edited by martindwilson; 07-15-2010 at 09:35 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    07-15-2010
    Location
    Vancouver
    MS-Off Ver
    Excel 2008
    Posts
    2

    Re: lookup exact match returns TRUE or FALSE

    Quote Originally Posted by NBVC View Post
    Perhaps

    =IF(ISNUMBER(MATCH(A1,F:F,0)),EXACT(A1,INDEX(F:F,MATCH(A1,F:F,0))),FALSE)

    where A1 is being matched to column F
    Fantastic, thank you so much!!

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: lookup exact match returns TRUE or FALSE

    i really think you should look at this before you go on! match only checks the first instance it finds. but if you only have one instance and you are just checking case youd be ok
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    06-25-2016
    Location
    Bangladesh
    MS-Off Ver
    Xp 7
    Posts
    1

    Re: lookup exact match returns TRUE or FALSE

    Dear Sir
    I maintain different ledger sheet for accounting purposes. Maintaining all ledger sheet it became trouble for me. I want that all ledger will link with cash where i entry one voucher automatically it will transfer to that ledger sheet. That means cash related expenses and income will link that account. I will entry in cash book opposit account will reach to that ledger.
    If any excel page remain in you Please send my email-shahinmir@yahoo.com.......

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2301 (Windows 11 22H2 64-bit)
    Posts
    65,652

    Re: lookup exact match returns TRUE or FALSE

    Welcome to the forum!

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

    Thread closed.
    Last edited by AliGW; 11-05-2016 at 02:24 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

Closed 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