Find Value by row and column, with offset?

t3chie

New Member
Joined
Jan 21, 2012
Messages
26
*4/1/13Merged Cell4/2/13Merged Cell
IntervalForecasted salesActual SalesForecasted salesActual Sales
7:0080507545
7:3085253525
8:0075354535

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

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

konew1

Well-known Member
Joined
Oct 17, 2007
Messages
2,287
Hi
This should do it, You will need ensure you always label the columns "forecast sales" and "actual sales" the same

Excel Workbook
ABCDE
11/04/20132/04/2013
2IntervalForecasted salesActual SalesForecasted salesActual Sales
37:0080507545
47:30852535125
58:0075354535
6
7lookup prameters
8date2/04/2013
9interval7:30
10fcst/actualActual sales
11value25
Sheet4
 

t3chie

New Member
Joined
Jan 21, 2012
Messages
26
[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

*ABCDE
1*1/04/20132/04/2013
2IntervalForecasted salesActual SalesForecasted salesActual Sales
37:0080507545
47:30852535125
58:0075354535
6*****
7lookup prameters****
8date2/04/2013***
9interval7:30***
10fcst/actualActual sales***
11value25***

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

New Member
Joined
Jan 21, 2012
Messages
26
[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

*ABCDE
1*1/04/20132/04/2013
2IntervalForecasted salesActual SalesForecasted salesActual Sales
37:0080507545
47:30852535125
58:0075354535
6*****
7lookup prameters****
8date2/04/2013***
9interval7:30***
10fcst/actualActual sales***
11value25***

<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
CellFormula
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,923
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top