# Find Value by row and column, with offset?

#### t3chie

##### New Member
 * 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!

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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

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

 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!

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

 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!

Replies
4
Views
198
Replies
2
Views
114
Replies
0
Views
124
Replies
2
Views
147
Replies
6
Views
556

1,219,958
Messages
6,151,147
Members
451,011
Latest member
Pigdog89

### 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?

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