How to match some lines according to a criteria in Excel?

Posted on 2011-05-10
Last Modified: 2012-06-27
I have an excel sheet which i am sending in the attachment.

In sheet 1 I have this:

Name       Group
James      A
Brian      B
Richard      D
Jake              C
Lora         B

In Sheet 2:

Name       Department
Jake              Dev
James      QE
Richard      Dev
Lora              QE
Brian      Dev

What I want to make in Sheet 1 is:

Name       Group     Department
James      A            QE
Brian      B            Dev
Richard      D            Dev
Jake              C            Dev
Lora         B            QE

This is just an example and the actual list is so long.

How can I make it diagrammatically in Excel?



Question by:Tolgar
    LVL 85

    Accepted Solution

    In C2 on sheet1 enter:
    and copy down.


    Author Comment

    Thank you it worked. But now I cannot count how many Dev and QE are in the Department column by simply doing ctrl + F and finding all.

    How can I count them?

    LVL 85

    Assisted Solution

    by:Rory Archibald
    You can use a formula like:

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Array Formula to add additional step 1 22
    Using "ScreenUpdating" 6 36
    Protecting an object 3 25
    Min Month2 5 16
    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

    733 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