Populating a cell based on data from 2 different cells

[SchedulerCody]

Let me start by saying I am an Excel novice.
I am trying to populate one cell of a spreadsheet comparing data from two separate cells. Let me explain...

I have a table of data that looks like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Phase[/TD]
[TD]Date[/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD]ENG[/TD]
[TD]10/1/2014[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]ENG[/TD]
[TD]10/2/2014[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]PRO[/TD]
[TD]10/1/2014[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]PRO[/TD]
[TD]10/2/2014[/TD]
[TD]D[/TD]
[/TR]
</tbody>[/TABLE]


I am trying to use this data to create a schedule that will populate the 'Output' based on the 'Phase' and 'Date'. It should look like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]10/1/2014[/TD]
[TD]10/2/2014[/TD]
[/TR]
[TR]
[TD]ENG[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]PRO[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
</tbody>[/TABLE]

My issue is that I do not know how to get 'A,B,C,D' to populate in those cells, based on the 'Date' and the 'Phase' of the previous table.

Does this make sense? Can anyone help?

Thanks in advance!

 

[Asala42]

That's trickier than it sounds. I was able to get this array formula to work:
{=INDEX($C$1:$C$5,MIN(IF(($B$1:$B$5=B$9)*($A$1:$A$5=$A10),ROW($B$1:$B$5))))}

Where:
- A1:C5 is the first table
- The initial formula cell is B10 (where A should appear)
Hit Ctrl+Shift+Enter to get the brackets.

You could probably use a simpler formula like a VLOOKUPS if you were willing to use a helper column.

 

[SchedulerCody]

I am willing to use a helper column, if you have any ideas. Simpler is better for me.
I do have a better understanding of VLOOKUP than I do of Arrays.

 

[vds1]

Lets say your first Table is from A1 to C5

and second table from E1 to G3 where E2 =ENG and F1=10/1/2014 then in F2 save the below formula and copy across.

=INDEX($C$1:$C$5,SUMPRODUCT(--($A$1:$A$5=$E2)*--($B$1:$B$5=F$1)*ROW($C$1:$C$5)))

Here the sum product returns the row number for column C based on Phase and Date. Index return the data from the resultant row number of sum product.

 

[vds1]

One more efficient way using array formula

Confirm with control+shift+enter

=INDEX($C$1:$C$5,MATCH($E2&F$1,$A$1:$A$5&$B$1:$B$5,0))

 

[Asala42]

You should test out the formulas given so far, but I'll describe the helper column method.

The helper column method involves inserting a formula to the left of your data that you can use towards a vlookup formula. Say we shifted your first table to columns B1:D5. We can populate A2:A5 with the formula:
=B2&C2

This merges the Phase text and Date into one single string. Ugly probably but it's something we can use for a VLOOKUP. So instead of the above formulas you can use:

=VLOOUP($A10&B$9,$A$1:$D$5,4,0)

Where:
A10 in your example contains "ENG" from your second table
B9 contains the date 10/1/2014, again from your second table
A1:D5 is your first table, including the helper column
4 is the column number data is pulled when the lookup finds a match in column A.

In this case you probably want to hide the text in A2:A5 by matching the text to the background color.

 

[SchedulerCody]

Asala42:
Thank you for this. As a novice, your solution seems simpler and makes much more sense to me. However, when I try and do the =B2&C2 formula I get something that looks like this 'ENG41913', when I think I should be getting something that looks like this 'ENG10/01/2014'. I assume that this is a formatting issue so I am currently playing around with that. Any ideas?

Thank you everyone!

 

[SchedulerCody]

Figured it out
=B2&" "&TEXT(C2,"MM/DD/YYYY")
If anyone has a more straightforward solution, I'd love to hear it.

 

[SchedulerCody]

One more thing. What if there are two or more cells that meet the same criteria?
i.e. multiple cells that have ENG and 10/01/2014 with different outputs (A,D,F,I).