Index Match returns #n/a on some cells and not others! driving me crazy!

dhune

New Member
Joined
Mar 6, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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?
 

Attachments

  • Index Match 1.jpg
    Index Match 1.jpg
    239.7 KB · Views: 8
  • Index Match 2.jpg
    Index Match 2.jpg
    237.8 KB · Views: 7

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,090
Office Version
  1. 2016
Platform
  1. Windows
Why are you using an array-formula?
Use column A seconds?
You can set the format of column A to number. Then you can compare with column J (also set to number). Control the numbers behind the comma.
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,090
Office Version
  1. 2016
Platform
  1. Windows
Maybe you need to convert column A: =INT(A1)+TIME(HOUR(A1),MINUTE(A1),0)
 
Solution

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,021
Office Version
  1. 2010
Platform
  1. Windows
I can't upload a mini sheet as the first column A has 50000 rows

Using XL2BB to include even the first few of many similar rows can be helpful because it shows your exact formulas. That said, you did a good job of the alternative, presenting and explaining repetitive formulas.

pressed ctrl+shift+enter after completing the index match formula in column K

I see no reason to array-enter that formula. Although there is no harm, it is better not to. It makes it easier to edit.

I have this formula in column J =I1+TIME(0,15,0)

You are probably encountering problems with 64-bit binary floating-point arithmetic.

In general, whenever we need a calculation to be accurate to some degree of precision (decimal places or, in this case, minutes or seconds), we should explicitly round to that degree of precision -- and not to arbitrary number of decimal places like 10, as some people suggest.

Change the calculation in column J to something like the following to round to the minute:

=INT(I1+TIME(0,15,0)) + TEXT(I1+TIME(0,15,0), "h:m")

Likewise, I suspect that column A should be populated with formulas of the following form:

=INT(A1+TIME(0,15,0)) + TEXT(A1+TIME(0,15,0), "h:m")

The INT part captures the date part without relying on the regional date format, which might vary if you share the workbook.

To demonstrate:


Select or hover the cursor over cells to see formulas.

Column C shows the internal numeric value of the date/times in column A, formatted to display 15 significant digits, the most that Excel formats. Column D shows the invisible residual offset, effectively the additional precision that Excel does not display. Note the difference between A3 and A2; and note that A4 and A2 are identical.
 
Last edited:

dhune

New Member
Joined
Mar 6, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Using XL2BB to include even the first few of many similar rows can be helpful because it shows your exact formulas. That said, you did a good job of the alternative, presenting and explaining repetitive formulas.



I see no reason to array-enter that formula. Although there is no harm, it is better not to. It makes it easier to edit.



You are probably encountering problems with 64-bit binary floating-point arithmetic.

In general, whenever we need a calculation to be accurate to some degree of precision (decimal places or, in this case, minutes or seconds), we should explicitly round to that degree of precision -- and not to arbitrary number of decimal places like 10, as some people suggest.

Change the calculation in column J to something like the following to round to the minute:

=INT(I1+TIME(0,15,0)) + TEXT(I1+TIME(0,15,0), "h:m")

Likewise, I suspect that column A should be populated with formulas of the following form:

=INT(A1+TIME(0,15,0)) + TEXT(A1+TIME(0,15,0), "h:m")

The INT part captures the date part without relying on the regional date format, which might vary if you share the workbook.

To demonstrate:


Select or hover the cursor over cells to see formulas.

Column C shows the internal numeric value of the date/times in column A, formatted to display 15 significant digits, the most that Excel formats. Column D shows the invisible residual offset, effectively the additional precision that Excel does not display. Note the difference between A3 and A2; and note that A4 and A2 are identical.
Awesome! That did it! You sir (or ma'am) just saved my sanity. Thank you very much.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,352
Messages
5,635,780
Members
416,880
Latest member
kyleswanson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top