0

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?

9
  • 1
    could you please add some sample data, both for working and not working? Feb 27, 2017 at 11:00
  • @MátéJuhász Can you tell me how to add data inside my question, because I don't know how to do that..
    – Michthan
    Feb 27, 2017 at 11:03
  • 1
    You can format your data as a table e.g. here). Then just insert it into your question Feb 27, 2017 at 11:07
  • I did what you asked. Thanks for the good tip :D.
    – Michthan
    Feb 27, 2017 at 11:14
  • Which are the rows where your formula works / fails? Can you please also add column headers? Feb 27, 2017 at 11:18

1 Answer 1

0

The named range TORF was made by using the OFFSET formula. In this the width of the offset range was declared as 1:1 instead of $1:$1. Changing solved the problem for me.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .