# Help with previous post

#### Peter1973

##### Well-known Member
I previously asked for help but post was not clear hope this can explain it correctly .
In sheet 1 I have :
In cells A1:A1000 I have various codes. General format ie G2004
In Cells B1:B1000 I have various times. In hh:mm format
In Cells C1:C1000 I have various days. In ddd format.

In sheet 2 I have :
In cells A1 I have a code.
In cells B1 have a time in hh:mm format.

I need a formula to look in Sheet 2 A1 then match that code in Sheet 1 A1:A1000, then once it has found the code look in the cell that is 3 colums to the right and 5 rows down from the code. Then in that cell will be a time i need it then to subtract sheet 1 B1 from that time ( ie 23:23 - B1 if B1 was 0:20 answer would be 23:03 ).
Then iff possible if that time between 21:00 and 23:00 return the value in the cell 5 columns to the right and 6 rows down from the code. Otherwise return zero.

Hope this is understandable as it is killing me thinking about it.

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### iliace

##### Well-known Member
I need a formula to look in Sheet 2 A1 then match that code in Sheet 1 A1:A1000

=MATCH(Sheet2!A1,Sheet1!\$A\$1:\$A\$1000,0)

then once it has found the code look in the cell that is 3 colums to the right and 5 rows down from the code.

=OFFSET(Sheet1!\$A\$1,MATCH(Sheet2!A1,Sheet1!\$A\$1:\$A\$1000,0)+5,3)

Then in that cell will be a time i need it then to subtract sheet 1 B1 from that time ( ie 23:23 - B1 if B1 was 0:20 answer would be 23:03 ).

I assume you meant Sheet 2.

=OFFSET(Sheet1!\$A\$1,MATCH(Sheet2!A1,Sheet1!\$A\$1:\$A\$1000,0)+5,3)-Sheet2!B1

Then iff possible if that time between 21:00 and 23:00 return the value in the cell 5 columns to the right and 6 rows down from the code. Otherwise return zero.

6 rows down and 5 columns to the right from the code on sheet 1 (another assumption). It'd be something like this:

Code:
``=IF(AND(OFFSET(Sheet1!\$A\$1,MATCH(Sheet2!A1,Sheet1!\$A\$1:\$A\$1000,0)+5,3)-Sheet2!\$B\$1>=("21:00"+0),OFFSET(Sheet1!\$A\$1,MATCH(Sheet2!A1,Sheet1!\$A\$1:\$A\$1000,0)+5,3)-Sheet2!\$B\$1<=("23:00"+0)),OFFSET(Sheet1!\$A\$1,MATCH(Sheet2!A1,Sheet1!\$A\$1:\$A\$1000,0)+6,5),0)``

Last edited:

#### Peter1973

##### Well-known Member
I have tried this and the way you explained it it should work as that is eactly what I want it to do ( your assumptions were right ) yet it keeps returning #VALUE

#### Peter1973

##### Well-known Member
Is there anyone who would be able to assist with this as no matter what I try it keeps returning the #VALUE

#### Gerald Higgins

##### Well-known Member
I tried replicating this, partly to educate myself on OFFSET and MATCH, which I'm not very familiar with - thanks iliace for the example !

I was basically able to get Iliace's formula to work without returning a #value error.

Are you sure you've typed it in correctly ?

I got a #VALUE error when the formula referred to sheets that didn't exist. Is it possible you've done something like this ?

#### Peter1973

##### Well-known Member
I have amended the formula slightly to match the names of my sheets and locations it is as follows:

=IF(AND(OFFSET('Order Creation'!\$C\$6,MATCH('NP Per Hour'!\$B\$5,'Order Creation'!\$C\$6:\$C\$4004,0)+5,3)-'NP Per Hour'!\$E\$5>=("21:00"+0),OFFSET('Order Creation'!\$C\$6,MATCH('NP Per Hour'!\$B\$5,'Order Creation'!\$C\$6:\$C\$4004,0)+5,3)-'NP Per Hour'!\$E\$5<=("23:00"+0)),OFFSET('Order Creation'!\$C\$6,MATCH('NP Per Hour'!\$B\$5,'Order Creation'!\$C\$6:\$C\$4004,0)+6,5),0)

I have replaced Sheet1\$A\$1 with Order Creation\$C\$6
Sheet2\$A\$1 with NP Per Hour\$B\$5
Sheet1\$A\$1:\$A\$1000 with Order Creation \$C\$6:\$C\$4004

It is still returning the #VALUE

Replies
3
Views
37
Replies
3
Views
341
Replies
2
Views
170
Replies
4
Views
100
Replies
8
Views
206

1,191,707
Messages
5,988,218
Members
440,138
Latest member
yanaa

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

### Which adblocker are you using?

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

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