0

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?

Improve this question
4
  • 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?
    – Michthan
    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...?
    – LenaH
    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/…
    – Michthan
    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...
    – LenaH
    Sep 26, 2017 at 7:21

1 Answer 1

Reset to default
1

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, when I need to look for multiple occurences I always consult this site.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.