Find Value by row and column, with offset?

[t3chie]

*	4/1/13	Merged Cell	4/2/13	Merged Cell
Interval	Forecasted sales	Actual Sales	Forecasted sales	Actual Sales
7:00	80	50	75	45
7:30	85	25	35	25
8:00	75	35	45	35

<tbody>
</tbody>

Hey!

My formatting of the sheet goes on continuously to the right for month of April. What I have is two formulas that calculate the current interval and current date, I want to use those to triangulate the actual sales for that interval. Example: Say it's 8:00 and it's 4/2/13, I want to find "35" on "Actual Sales of the 4/2/13.

The reason it has to be offset is because this is a formatted report that has the dates merged over the data to report to management. So I'm assuming it would be a mix of an index and match and would need to be offsetted from the date column to get to the "Actual Sales" column to get the 35 on 4/2/13 at 8:00.

Thanks for your time!

 

[konew1]

Hi
This should do it, You will need ensure you always label the columns "forecast sales" and "actual sales" the same

Excel Workbook
	A	B	C	D	E
1		1/04/2013		2/04/2013
2	Interval	Forecasted sales	Actual Sales	Forecasted sales	Actual Sales
3	7:00	80	50	75	45
4	7:30	85	25	351	25
5	8:00	75	35	45	35
6
7	lookup prameters
8	date	2/04/2013
9	interval	7:30
10	fcst/actual	Actual sales
11	value	25
Sheet4

 

[t3chie]

[Resolved] Find Value by row and column, with offset?

Hi
This should do it, You will need ensure you always label the columns "forecast sales" and "actual sales" the same

Sheet4

*	A	B	C	D	E
1	*	1/04/2013	2/04/2013
2	Interval	Forecasted sales	Actual Sales	Forecasted sales	Actual Sales
3	7:00	80	50	75	45
4	7:30	85	25	351	25
5	8:00	75	35	45	35
6	*	*	*	*	*
7	lookup prameters	*	*	*	*
8	date	2/04/2013	*	*	*
9	interval	7:30	*	*	*
10	fcst/actual	Actual sales	*	*	*
11	value	25	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:102px;"><col style="width:102px;"><col style="width:102px;"><col style="width:102px;"><col style="width:102px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B11 =INDEX(A1:E5,MATCH(B9,A1:A5,0),MATCH(B8,A1:E1,0)+IF(B10="Actual Sales",1,0)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

This worked perfectly! You are a gentleman and a scholar!

 

[t3chie]

[Resolved] Find Value by row and column, with offset?

Hi
This should do it, You will need ensure you always label the columns "forecast sales" and "actual sales" the same

Sheet4

*	A	B	C	D	E
1	*	1/04/2013	2/04/2013
2	Interval	Forecasted sales	Actual Sales	Forecasted sales	Actual Sales
3	7:00	80	50	75	45
4	7:30	85	25	351	25
5	8:00	75	35	45	35
6	*	*	*	*	*
7	lookup prameters	*	*	*	*
8	date	2/04/2013	*	*	*
9	interval	7:30	*	*	*
10	fcst/actual	Actual sales	*	*	*
11	value	25	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:102px;"><col style="width:102px;"><col style="width:102px;"><col style="width:102px;"><col style="width:102px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B11 =INDEX(A1:E5,MATCH(B9,A1:A5,0),MATCH(B8,A1:E1,0)+IF(B10="Actual Sales",1,0)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

This worked perfectly! You are a gentleman and a scholar!