0

I have a sheet with exact values (verified by =a2=b2 returning true).

However, =match(a2,b2,0) returns #N/A for some values, but not all.

All cells are formatted the same (I've tried General, Text, Number, etc.), and I have no idea where to go from here.

I don't know if I can upload my test workbook here so here's an image:

image

Grant Miller
  • 27,532
  • 16
  • 147
  • 165
Zach Shaner
  • 43
  • 9
  • What formula have you used in column D to verify that A=B? – XOR LX Aug 26 '16 at 07:32
  • What is the purpose of your MATCH formula? Using MATCH just to compare the two values is possible but a simple IF will do the same in this case. – Gabor Aug 26 '16 at 07:32
  • @Gabor True from a practical point of view, but the theoretical point remains of interest, in my opinion, i.e. if =A2=B2 returns TRUE, why should =MATCH(A2,B2,0) error? – XOR LX Aug 26 '16 at 07:36
  • @XOR LX, from a theoretical point of view I'm curious as well. It would be good to see the actual file, or at least the formulas in the file. Can you post another picture where you show the formulas in the cells? – Gabor Aug 26 '16 at 07:36
  • @XORLX [here](http://imgur.com/a/rKESM) and [here](http://imgur.com/a/l1fjc) – Zach Shaner Aug 26 '16 at 08:18
  • Why does your title contain a reference to the function SUMIF? – XOR LX Aug 26 '16 at 08:21
  • Also, are you set to Automatic Calculation mode (File/Options/Formulas)? – XOR LX Aug 26 '16 at 08:23
  • @XORLX I originally came across the problem using SUMIF and while investigating condensed it into this smaller sheet. In retrospect mentioning SUMIF was unnecessary. My apologies. And yes I do have automatic calc on – Zach Shaner Aug 26 '16 at 08:33
  • If you go into the formula bar for one of the cells containing a MATCH formula which errors, then press ENTER, does the result change? – XOR LX Aug 26 '16 at 08:35
  • @XORLX it recalculates the error. – Zach Shaner Aug 26 '16 at 08:37
  • For one of those errors, can you post a screenshot of the Evaluate Formula box (Formulas tab) after having pressed the Evaluate button a single time (but not a second)? – XOR LX Aug 26 '16 at 08:44
  • @XORLX http://imgur.com/a/SDc9A so it looks like the error is with the b column? – Zach Shaner Aug 26 '16 at 08:51
  • What are the cell formats for cells A2 and B2? – XOR LX Aug 26 '16 at 08:53
  • @XORLX right now they are "General" but I've tried changing both of them to almost every possible combination – Zach Shaner Aug 26 '16 at 08:57
  • Ok, we'll get there! Can you also post a screenshot of the Evaluate Formula box for the formula =A2=B2, though this time having pressed the Evaluate button twice? – XOR LX Aug 26 '16 at 08:58
  • @XORLX Thanks for all your help! http://imgur.com/02fkess EDIT: Oops... don't know what happened there... here is the non messed up version http://imgur.com/a/s4xr8 – Zach Shaner Aug 26 '16 at 09:03
  • Thanks. And if you go into the formula bar for that formula and press ENTER, do you still get TRUE as the return? – XOR LX Aug 26 '16 at 09:05
  • @XORLX Yep. If I click evaluate once more it still says TRUE as well – Zach Shaner Aug 26 '16 at 09:09
  • Are the entries in A2 and B2 static values, or the results of formulas in those cells? Also, do you currently have any circular references within any open workbooks? – XOR LX Aug 26 '16 at 09:10
  • @XORLX Completely static. They were originally results of a formula but I have long since copy>pasted values. Also this is currently my only workbook open however I did get an error message about circular references earlier today. – Zach Shaner Aug 26 '16 at 09:19
  • Do you still have that circular reference? (It should say in the bottom-right of your Excel window, or you can also go to Formulas/Error Checking/Circular References.) Sorry, bottom-left, I believe. – XOR LX Aug 26 '16 at 09:22
  • I do not still have the circular reference but when I went to error checking it said I had a trace error and when I clicked on it, it drew these lines http://imgur.com/a/ZzatC – Zach Shaner Aug 26 '16 at 09:29
  • Honestly, I would need to see your file directly to see what is going on in there. I've done a test with the same numbers and everything works fine for me. – Gabor Aug 26 '16 at 09:54
  • Can you please, do one more thing: Go to the Formulas tab, look for the Formula Auditing section and click the Show Formulas button. Then, send another screenshot of your table. – Gabor Aug 26 '16 at 09:57
  • @Gabor I would love to upload the file but I'm not 100% sure on what the rules are for that. If I could just upload it to a file sharing website and that would be allowed please let me know. Here is your screenshot http://imgur.com/DGbdt6x – Zach Shaner Aug 26 '16 at 10:03
  • I think you can put it on a sharing site and paste a link – Gabor Aug 26 '16 at 10:21
  • @Gabor https://drive.google.com/file/d/0B1XzwlJsvJIoZ0F3Y01aRndqOHM/view – Zach Shaner Aug 26 '16 at 10:30
  • Thanks for the file. I did a little test. When I go into both Value1 and Value2 cells on row 2, and hit Enter in both of them, it gets recalculated and the proper value shows up in Match. It might be some formatting issue that is related to where the data came from originally. I have experienced this before, and this is very annoying indeed. – Gabor Aug 26 '16 at 10:57
  • @Gabor Do you know of a way to fix this without manually hitting enter? I have thousands of lines that have this bug. – Zach Shaner Aug 26 '16 at 11:02

1 Answers1

1

Maybe run a quick macro on the relevant cells in columns A and B:

Sub Clean_Up()

For Each r In Range("A2:B8")
    r.Value = Val(r.Value)
Next r

End Sub

Adjust range (A2:B8 here) to suit.

Regards

XOR LX
  • 7,632
  • 1
  • 16
  • 15