Hi, i'm currently using index match to retrieve values from an array with 50000 rows. My problem is with the match lookup value and array part. The lookup value is a date with dd/mm/yy hh:mm format and the lookup array is in the same format (column J and A). I can't upload a mini sheet as the first column A has 50000 rows and if I just cut the first few rows at the top, it won't work because the values I want to lookup are thousands of rows down in column A, so I hope the pictures will make sense.

In column J where I have my lookup values, I have different dates with similar times of the day (hh:mm). Now, I want to retrieve the values from column G into column K so i have the formula =INDEX($G$1:$G$50000,MATCH(J1,$A$1:$A$50000,0)). I copied the values in column I from a different source which has different dates but with similar times (either 8:30:00 or 9:30:00 am). I want to retrieve values from column G corresponding to the dates in column J but with times 8:45 am and 9:45 am (not 8:30 am and 9:30 am) so I just added 15 minutes to the values in column I and I have this formula in column J =I1+TIME(0,15,0). The weird thing is the index match formula returns values in column K but only on the rows where the corresponding times are at 9:45 and then returns #N/A in the cells with corresponding times at 8:45 am! I checked the formatting on column A and J to make sure they are the same and also pressed ctrl+shift+enter after completing the index match formula in column K. The puzzling thing is, if I delete cell J1 and type the date and time MANUALLY instead of using the formula J1=I1+TIME(0,15,0) to get 8:45 am, the index match formula returns a value! I've tried copy pasting column J as values to remove the formulas but to no avail. I still get #N/A for the 8:45 times. The values do exist in column A, that is for sure, I checked them manually. I don't get why the formula would work on the cells where the corresponding time I want to retrieve from is at 9:45am and not those where the time is 8:45am...

I did copy the values in column I from an internet page then pasted them in excel, not sure if this would be causing my problem?

In column J where I have my lookup values, I have different dates with similar times of the day (hh:mm). Now, I want to retrieve the values from column G into column K so i have the formula =INDEX($G$1:$G$50000,MATCH(J1,$A$1:$A$50000,0)). I copied the values in column I from a different source which has different dates but with similar times (either 8:30:00 or 9:30:00 am). I want to retrieve values from column G corresponding to the dates in column J but with times 8:45 am and 9:45 am (not 8:30 am and 9:30 am) so I just added 15 minutes to the values in column I and I have this formula in column J =I1+TIME(0,15,0). The weird thing is the index match formula returns values in column K but only on the rows where the corresponding times are at 9:45 and then returns #N/A in the cells with corresponding times at 8:45 am! I checked the formatting on column A and J to make sure they are the same and also pressed ctrl+shift+enter after completing the index match formula in column K. The puzzling thing is, if I delete cell J1 and type the date and time MANUALLY instead of using the formula J1=I1+TIME(0,15,0) to get 8:45 am, the index match formula returns a value! I've tried copy pasting column J as values to remove the formulas but to no avail. I still get #N/A for the 8:45 times. The values do exist in column A, that is for sure, I checked them manually. I don't get why the formula would work on the cells where the corresponding time I want to retrieve from is at 9:45am and not those where the time is 8:45am...

I did copy the values in column I from an internet page then pasted them in excel, not sure if this would be causing my problem?