Not sure which formula to use

[DEHA11]

I inherited a spreadsheet that has been manually populated in the past but I know a formula exists to make this work....

There are two tabs:

The "Summary" tab looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]EMPLOYEE[/TD]
[TD]REGULAR[/TD]
[TD]SPECIAL[/TD]
[TD]OTHER[/TD]
[TD="align: right"]DATE:[/TD]
[TD]1/1/2018[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]J Smith[/TD]
[TD="align: center"]500.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]T Doe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]K Timm[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The "Data" tab looks like this with the exception that the date is in merged cells rather than listed in each column.
(ie B1:D1 is one cell that has 1/1/2018 in it, E1:G1 is one cell that has 1/15/2018 in it)

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]1/1/2018[/TD]
[TD]1/1/2018[/TD]
[TD]1/1/2018[/TD]
[TD]1/15/2018[/TD]
[TD]1/15/2018[/TD]
[TD]1/15/2018[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Employee[/TD]
[TD="align: center"]Regular[/TD]
[TD="align: center"]Special[/TD]
[TD="align: center"]Other[/TD]
[TD="align: center"]Regular[/TD]
[TD="align: center"]Special[/TD]
[TD="align: center"]Other[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]J Smith[/TD]
[TD="align: center"]500.00[/TD]
[TD="align: center"]100.00[/TD]
[TD="align: center"]50.00[/TD]
[TD="align: center"]400.00[/TD]
[TD="align: center"]50.00[/TD]
[TD="align: center"]10.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]T Doe[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"]200.00[/TD]
[TD="align: center"]20.00[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"]200.00[/TD]
[TD="align: center"]20.00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]K Timm[/TD]
[TD="align: center"]100.00[/TD]
[TD="align: center"]100.00[/TD]
[TD="align: center"]20.00[/TD]
[TD="align: center"]100.00[/TD]
[TD="align: center"]100.00[/TD]
[TD="align: center"]20.00[/TD]
[/TR]
</tbody>[/TABLE]


I'd like the empty cells (B2:E4) on the Summary tab to automatically update with the corresponding information from the Data tab when I change the date in cell F1.

So, cell B2 on the Summary tab would be populated with the information from cell B3 from the Data tab based on matching "J Smith", the date, and "Regular" and so on.

I can make changes to the overall layout if necessary to make this work, like un-merging the date cells on the data tab. I've tried INDEX MATCH and SUMIFS, but neither are working with the desired result.

Help is greatly appreciated!
-Deha

 

[BiocideJ]

Based on the specifics of your table set up (and I'm assuming the Regular, Special, Other [3 columns] are repeated for all dates) the following formula pasted in cell B2 of your summary sheet and copied across and down as necessary will give you the expected output.
=INDEX(Data!$1:$1048576,MATCH(Summary!$A2,Data!$A:$A,0),MATCH($F$1,Data!$1:$1,0)+COLUMN(B$1)-2)

FYI: no changes to the DATA tab would be necessary.

 

[DEHA11]

...the following formula pasted in cell B2 of your summary sheet and copied across and down as necessary will give you the expected output. =INDEX(Data!$1:$1048576,MATCH(Summary!$A2,Data!$A:$A,0),MATCH($F$1,Data!$1:$1,0)+COLUMN(B$1)-2)

FYI: no changes to the DATA tab would be necessary.

This worked! THANK YOU!!

 

[BiocideJ]

No problem. Thanks for confirming.