# Error with index and match in Excel

I have two related questions:

1) First, I have a problem with INDEX and MATCH in Excel and cannot solve it using the usual tips. Here's my code:

``````=INDEX(\$D\$2:\$D\$1000;MATCH(F\$1;\$B\$2:\$B\$1000;0);1)
``````

whereas `D` contains dates and `B` contains prices formatted as numbers.

`F1` contains a specific date. What's wrong? I get #N/A.

2) Second, even if I get this to work, my problem is that `D` contains 24 times the same date (i.e. `B` contains hourly prices). I would like to paste all 24 prices for one date in one column in the order that they are listed in `D`, and the next date in the next column etc.

Is INDEX and MATCH even the right approach for this?

Or Vlookup better?

How would I do it?

• You have the answer to your first question in the question itself: You say that F contains a date and D contains date, shouldn't it be: INDEX(\$B\$2:\$B\$1000;MATCH(F\$1;\$D\$2:\$D\$1000;0);1). As to your second question, can't you work with datetimes? So the date and time in same cell, that would solve your problem no? Sep 25, 2017 at 12:40
• Thanks - regarding 1), oops yes, you are right, it works now! Regarding 2) No, for different reasons I cannot - but isn't there a possibility to show the 1st match, then the 2nd match etc. up to 24th, and then I can copy the formula across for the other dates...? Sep 25, 2017 at 17:56
• Yes there is, but I'm no specialist either. I always use the following link, because it makes it clear for me, and is also quite funny: chandoo.org/wp/2014/12/09/… Sep 26, 2017 at 7:11
• OK, thanks. If you post your reply as an answer, I'll accept it and look for solutions for no. 2 or open a separate post... Sep 26, 2017 at 7:21

You have the answer to your first question in the question itself: You say that `F` contains a date and `D` contains date, shouldn't it be:
``````INDEX(\$B\$2:\$B\$1000;MATCH(F\$1;\$D\$2:\$D\$1000;0);1)