1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel 2003: Lookup One Cell with multiple lookup criteria

Discussion in 'Business Applications' started by ColoradoSteve, Jun 29, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. ColoradoSteve

    ColoradoSteve Thread Starter

    Joined:
    Jun 29, 2010
    Messages:
    1
    Help!!!

    I have a client I am going back to tomorrow and need an answer quickly so I look like I know what I am doing. My client has a downloaded table of data that has several columns of data including employee names and safety courses they are taking. I need to find the cell with the completion date every employee for every course they took. If there was one employee row or column per employee,with multiple classes, or one row or column per class with multiple columns for each employee I would know what to do. For example I could do a combination of index and match functions to select a row and then select a column to lookup the cell.

    However there is a row for every class an employee took, with the employee name and class in the row. Each employee has multiple rows with a row for each class they took. There must be a way to perform a lookup that is row oriented that looks up one criteria and then the next to return the right value. I suppose that I could use nested if statements to do this, but there must be a better/less cumbersome way. I am hoping there is a relatively clean and simple way to do this that I am missing. By the way I asked whether they could download the employee names and make each name one column and they told me they could not.

    Any help would be greatly appreciated and save me some work that is urgently needed right now.

    Thanks

    Colorado Steve
     
  2. mattyb33

    mattyb33

    Joined:
    Jun 29, 2010
    Messages:
    3
    Could you concatenate the employee name and the course and then do a lookup based on that formula?
     
  3. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    This sounds like one for a Pivot Table.
     
  4. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    If you need more assistance, please post up a small sample - with sensitive data removed. So we can see the structure.
     
  5. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Providing all rows of data are contiguos with field headings for each column, then the simplest and most effective way is to use Data, Filter, Autofilter.

    Using the example attached, click on A1, then select Data, Filter, Autofilter

    Click on the arrow in A1 and select A
    You should only see items for person A

    Click on the Arrow in C1 and select Yes
    You should only see one item.

    To get back to the original data click on Data, Filter, Autofilter, Restore (I think). I am using version 2007.

    You should see all your records back to normal.

    HTH
     

    Attached Files:

  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/932345

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice