I have two sheets that I am using. In the first sheet there are errors and the time at which an error occurs.
A B C D E F
| | | | | Datetime | |
|---|---|---|---|----------------|---|
| | | | | 3/01/2017 8:54 | |
| | | | | 3/01/2017 8:57 | |
| | | | | 3/01/2017 8:59 | |
The second sheet comprises of products and the end time of the production of that certain product. Notice here that there are no titles. In row 1 the data just begins.
A B C D E F G
| 325 | 3/01/2017 0:00 | 10:25:52 | 3/01/2017 10:25 | 1278 G100941 | 1278 | G100941 |
| 326 | 3/01/2017 0:00 | 6:54:12 | 3/01/2017 6:54 | 1277 G121624 | 1277 | G121624 |
| 327 | 3/01/2017 0:00 | 5:45:04 | 3/01/2017 5:45 | 1276 G124920 | 1276 | G124920 |
| 328 | 3/01/2017 0:00 | 4:40:34 | 3/01/2017 4:40 | 1275 G100926 | 1275 | G100926 |
I'm now using an index-match formula to find the product that an error occurred on. This is the formula I'm using:
=INDEX(TORF,MATCH(E2,INDEX(TORF,0,4),-1),7)
With TORF being the named range that references to the second sheet.
Column E contains the time the error occurred.
The Fourth column of TORF contains the times production of a product ended and in the 7th column you can find the article code.
This formula works great for some times and doesn't for other times. The match part of the formula always works, but the index function is throwing a reference error sometimes.
For E2 the formula comes down to this:
=INDEX(TORF,326,7)
And it returns a value.
For E3 the formula comes down to this:
=INDEX(TORF,326,7)
And it returns a reference error. How can I prevent this from happening?