+ Reply to Thread
Results 1 to 6 of 6

Lookup that returns true value if cell matches text in a column

  1. #1
    Registered User
    Join Date
    06-25-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    14

    Lookup that returns true value if cell matches text in a column

    Hi all

    Would anyone be able to help me with this formula?

    Eg:
    Sheet 1 has Column A with a list of codes form one source.
    Sheet 2 has Column A with a list of codes from another source.

    If sheet2 has cell A1 as: ABC123, I want A2 to search for that string in sheet 1 Column A and return a True value if it is in that Column.

    I need the formula to put into Sheet 2 A2

    Thanks

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Lookup that returns true value if cell matches text in a column

    Hi Shauncat,

    Please Login or Register  to view this content.
    in B19.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Lookup that returns true value if cell matches text in a column

    or just,

    =ISNUMBER(COUNTIF(Sheet1!A1:A100,A1))

    Returns True or False
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  4. #4
    Registered User
    Join Date
    06-25-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Lookup that returns true value if cell matches text in a column

    Thanks for a fast response.
    Those formulas dont seem to return the correct response.
    I have attached a test doc for you to look at.

    Thanks

    Attachment 168538
    Attached Files Attached Files

  5. #5
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: Lookup that returns true value if cell matches text in a column

    in the above spreadsheet's Sheet2!B1:

    Please Login or Register  to view this content.
    and drag-fill down.

    this will give you a list of TRUE / FALSE (TRUE for those that exist in Sheet1!$A$1:$A$14, else FALSE).
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  6. #6
    Registered User
    Join Date
    06-25-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Lookup that returns true value if cell matches text in a column

    =SUMPRODUCT(ISNUMBER(SEARCH($A1,Sheet1!$A$1:$A$14))*1)>0
    That worked perfectly icestationzbra .
    Thank you so much for your help.

    Also thanks to xladept and Palmetto for your help.

+ 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