+ Reply to Thread
Results 1 to 8 of 8

Check values in two cells and return value of a third cell - Simple?

  1. #1
    Registered User
    Join Date
    01-16-2012
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2010, Office for Mac 2011
    Posts
    22

    Check values in two cells and return value of a third cell - Simple?

    I have been searching the forum this morning and haven't really stumbled on something that seems to work for my specific situation. I'm trying to figure out how to have excel look at two cells and return the value of a third. Example:

    UserID TrainingTitle TrainingCode CompletionDate
    john.doe Training ABCD-EF-100 1/15/2009 0:00
    john.doe Training1 FALSE 1/16/2009 0:00

    I need find a formula that will allow me to grab the User ID and the Completion date (since multiple users could potentially have the same completion date) in order to return the correct training code. I've been looking at VLOOKUP, INDEX, IF's and I'm just not sure which direction would best suite what I am trying to accomplish. I'm sure this should be fairly simple, but any input or help would be greatly appreciated.

    Becky

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Check values in two cells and return value of a third cell - Simple?

    It's not possible for you to work with an filter?
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,841

    Re: Check values in two cells and return value of a third cell - Simple?

    How about an array formula: =INDEX($C$2:$C$3,MATCH(1,(($A$2:$A$3=$A$6)*($D$2:$D$3=$A$7)),0))
    Entered with Ctrl + Shift + Enter

    Above formula assumes User ID is in A2:A3, Training Title in B2:B3, Training Code in C2:C3, and Completion Date in D2:D3
    john.doe is in cell A6
    1/16/09 is in cell A7

    - Moo

  4. #4
    Registered User
    Join Date
    01-16-2012
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2010, Office for Mac 2011
    Posts
    22

    Re: Check values in two cells and return value of a third cell - Simple?

    Thanks Moo! I'll give it a go. I have a few more columns in the middle then what my example showed
    B C D (in use) E F G H
    UserID / TrainingTitle / TrainingTitle1 / TrainingCode / TrainingType / MajorVersion / MinorVersion /

    I
    CompletionDate


    I don't need both rows checked - only one at a time. Sorry if my example was confusing! So I just need it to check john.doe and the date 01/01/2009 (or whatever it is) and return the proper training code.
    Last edited by rhybeka; 12-21-2012 at 02:30 PM.

  5. #5
    Registered User
    Join Date
    01-16-2012
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2010, Office for Mac 2011
    Posts
    22

    Re: Check values in two cells and return value of a third cell - Simple?

    Unfortunately no since I'm working with two different sheets. it would be fairly time consuming to do manually. I'd have to filter for the user ID and then check the date then flip back over to the original sheet and input the training code. Not really the way I want to spend an afternoon.

  6. #6
    Registered User
    Join Date
    01-16-2012
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2010, Office for Mac 2011
    Posts
    22

    Re: Check values in two cells and return value of a third cell - Simple?

    Still having an issue getting my brain around Moo's array so I thought I'd include some example data - names have been changed to protect the innocent. I can't help but think I'm missing something simple! Example.xlsx I'm just trying to leverage the user ID and completion date in the first sheet (value) to find the correct training code from the second sheet (source). I think I'm about to the point I'll just do it manually if I can't figure it soon, just wasn't looking forward to that

  7. #7
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,841

    Re: Check values in two cells and return value of a third cell - Simple?

    This will return the values based on your uploaded sample data, entered as an array formula (by pressing Ctrl + Shift + Enter, instead of just Enter after typing/pasting the formula in the formula bar):
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    That can be entered in cell C2 and filled down.

    NOTE: It will only return the FIRST value in the list on the source page matching the two specific requirements. If a person completes two Training Codes on the same day, only the first requirement found on the list for that date will be returned. Example, row 6 on the 'Values' sheet... you are expecting "Training4-190", but since the date on which that requirement was completed is the same as an earlier requirement ("Training1-100"), it will return the earlier requirement.

    Long story short - if you have a single person with two or more training codes completed on the same day, the list will only show the first training code, not the second.


    - Moo

  8. #8
    Registered User
    Join Date
    01-16-2012
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2010, Office for Mac 2011
    Posts
    22

    Re: Check values in two cells and return value of a third cell - Simple?

    Thanks Moo, that worked! I figured it would only pull the first value, but it really minimized the amount of manual work. Thanks again!

+ 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