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

1. ## 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. ## 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?

3. ## 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. ## 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.

5. ## 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. ## 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. ## 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:
`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. ## 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!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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