Determine if value is present in a given range in Excel

Posted on 2011-04-18
Last Modified: 2012-06-21

In Excel (2007), suppose Col A has a couple thousand entries which are random but all of the same format (General).  Furthermore, suppose that Col B also has numerous entries, some of which are identical to entries in Col A and some of which are not.

What formula would go in Col C to indicate, with a simple Yes or No response, whether the adjacent entry in Col B can also be found in Col A?

Here is a quick example (if Col A had only seven rows) showing the desired result in Col C:
 1I thought the correct formula would be something like:


but I can't quite get it to work.  Am I using the correct functions?


Question by:Steve_Brady
    LVL 30

    Expert Comment

    Put this in C1


    and drag it down :)

    LVL 30

    Accepted Solution

    In Fact Use this



    Author Comment

    I was just going to say the 1st one gave me #N/A's but the 2nd fixed that.  I'll study that solution so I understand it.

    LVL 30

    Expert Comment

    Steve, Would you like me to break it up?


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now