0

I have a very simple formula: =index(B:B,match(A1,B:B,0)). For some reason, though, the values only ever returns #N/A. However, as soon as I "enter" a cell in column B (click in the formula bar, double click cell, press enter, etc) and exit the cell without making any changes, that value is then recognized and found.

  • All data is being exported from online work tools, if that matters.

  • Formulas have been tested with columns A and B formatted as General, Number, and Text with no changes.

I have no idea why this is occurring. The formulas appear to be correct, but for some reason the column is formatted incorrectly or something.

  • do you have calculations set to automatic ? – PeterH Feb 11 at 15:48
  • @PeterH Wasn't aware that that was even setting so took a (tiny) bit of digging, but yes it is set to automatic. – Tock Feb 11 at 16:21
1

Many exports of data are not presented to Excel as "perfectly" numerical even when they are intended to be so.

A common thing people do in Excel is to have a value that has been treated as text by Excel and they either enter the editing mode with the cell selected, then leave making and saving no changes, just as you describe, yet the value is immediately changed in whatever final respects by Excel to be solely numerical or they copy a 1 and Paste|Special|Multiply hoping to achieve the same thing.

However, fairly often, neither works. (Folks don't tell you about that on the internet.) There are apparently still some characteristics that Excel is tracking that keep the change from happening. Not talking about non-breaking white spaces here. Just you format a cell as Text, actually personally enter a number, do the above, and it's a number. Do the same to an imported value though and it does not happen.

It seems pretty likely, since your data comes from other programs' exports, that this is what is happening, except that the change DOES occur nicely for you. Which is likely good news.

Try the following: freshly import some data into a fresh spreadsheet (or close, re-open, then import into the offending spreadsheet, but a brand new spreadsheet is better for the experiment since nothing can go wrong). Then type a 1 in some cell, somewhere near and Copy it. Highlight a few of the imported cells and Paste|Special|Multiply.

If the values are suddenly values (for example, the formula suddenly works if you were presenting one of them as the input in A1), then you have data that is "'twixt and 'tween."

If so, you can "clean" the data, so to speak, before use by either importing it elsewhere and doing the Paste|Special|Multiply trick, or import it to its new home, then do it. You can automate that with a macro instead.

If that does not work, it would be very helpful to folks here if you can add to you post a small sample of the just-freshly-imported data, before you've done anything else at all to it.

By the way, if you hear of "non-breaking spaces" or the CLEAN() function, perhaps from someone in your office, that is defintively NOT the problem here as it would not be solved by entering Edit mode and escaping.

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.