Returning Specific Data using IF statements

[chrono2483]

Hello,

I have a sheet that contains columns of merged cells that have dates, Sunday to Saturday, as the heading. Under which are 2 columns (b/c the date cell is merged), that contain information. Column 1 and 2. Column 1 is the side header for the data in column 2:

|Sunday, Nov 30|
|Service Level| 35%|

On a separate sheet, I want to be able to include a formula, so that it will recognize what day it is today, and pull data from the cell. I tried using "IF" statements, however it didn't work, as it kept giving me the data for Sunday, even it I reference today as "Dec 2".

This is my formula:

=IF('Notes'!A1:B1='HOME'!A1:B1,'HOME'B2,('Notes'!A1:B1='HOME'!D1:E1,'HOME'!E2,IF.....) [and so on until Saturday]

Were 'Notes'!A1:B1 is today's date in the new sheet [=today()]
'HOME'!A1:B1 is the date from original sheet
'HOME'B2/'HOME'!E2 are the data that I want returned

I hope this is clear, if not I can clarify.

Thank you!

 

[konew1]

This should give you the data you want, you can then use it in any calculations you need

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Sun, Nov 30, 2014		Mon, Dec 01, 2014		Tue, Dec 02, 2014		Wed, Dec 03, 2014		Thu, Dec 04, 2014
2	SL3	34%		36.00%		38.00%		40.00%		42.00%
3
4
5
6
7
8		30/11/2014	0.34
9		1/12/2014	0.36
10		2/12/2014	0.38
11		3/12/2014	0.4
Sheet1

 

[chrono2483]

Thank you for your response. I've tried that, as well as variations if index/match, IFs statements, as well as v and hlookup, but so far that doesn't seem to do the trick. Am I missing something?



Here is my sheet 1:


[TABLE="width: 640"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 137, bgcolor: transparent, align: center"]A
[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]B
[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]C
[/TD]
[TD="width: 132, bgcolor: transparent, align: center"]D
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]E
[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]F
[/TD]
[TD="width: 135, bgcolor: transparent, align: center"]G
[/TD]
[TD="width: 107, bgcolor: transparent, align: center"]H
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]1
[/TD]
[TD="bgcolor: yellow, colspan: 2, align: center"]Sunday Nov 30, 2014[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow, colspan: 2, align: center"]Monday Dec 01, 2014[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow, colspan: 2, align: center"]Tuesday Dec 02, 2014[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]2
[/TD]
[TD="bgcolor: transparent"]SL Forecast[/TD]
[TD="bgcolor: transparent"]40%[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]SL Forecast[/TD]
[TD="bgcolor: transparent"]38%[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]SL Forecast[/TD]
[TD="bgcolor: transparent"]45%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]3
[/TD]
[TD="bgcolor: transparent"]Calls[/TD]
[TD="bgcolor: transparent"]14244[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Calls[/TD]
[TD="bgcolor: transparent"]30364[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Calls[/TD]
[TD="bgcolor: transparent"]24777[/TD]
[/TR]
</tbody>[/TABLE]



Here is my sheet 2:

[TABLE="width: 199"]
<colgroup><col width="64" style="width: 48pt;"> <col width="137" style="width: 103pt; mso-width-source: userset; mso-width-alt: 5010;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 137, bgcolor: transparent, align: center"]A
[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]B
[/TD]

[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]1
[/TD]
[TD="bgcolor: yellow, colspan: 2, align: center"]Wednesday Dec 03, 2014
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]2
[/TD]
[TD="bgcolor: yellow, colspan: 2, align: center"]HOME[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]3
[/TD]
[TD="bgcolor: transparent"]SL Forecast[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]4
[/TD]
[TD="bgcolor: transparent"]Calls[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]


What I want to do is use sheet 2, and have a formula that will recognize what day it is (based on the =today() formula in Sheet2!A1), and with that find that date in sheet one, and return the particular data for that day (the SL and CV).

Can the index/match formula do this? Is there such a thing as combining IF statements with index/match, so that I could do (IF today's date matches the date in sheet 1, return that particular data)?

Thank you as always!

 

[konew1]

This will do it. My dates are UK style, but that is only formatting.

Excel Workbook
	B	C	D	E	F	G	H	I
1	2/12/2014			3/12/2014			4/12/2014
2	SL Forecast	40%		SL Forecast	38%		SL Forecast	45%
3	Calls	14244		Calls	30364		Calls	24777
Sheet1

Excel Workbook
	B	C
1	4/12/2014
2	HOME
3	SL Forecast	0.45
4	Calls	24777
Sheet2

 

[chrono2483]

@ konew1, that did the trick! Thank you so much!