0

I'm having some issues with the new excel release and an old Excel file with "=Index(Match(...))" formulas.

In the new version of excel, it's automatically writing "@" before the formula. "=@Index(Match(...))". I have a couple of questions:

  1. Do you know what does it do? or what is that for?
  2. Do you know how to remove them?

In some of the formulas I want Na() as a result, but it replaces the result by zeros... In previous versions of excel it is working.

Miguel Fabra
  • 63
  • 4
  • 11
  • Are you sure the cell format is general? – Andreas Nov 21 '19 at 16:44
  • 1
    It should mean that the formula is relying on *implicit intersection* to return the relevant result - e.g. if you used an entire column for the lookup values (1st argument to MATCH). – Rory Nov 21 '19 at 16:48
  • 1
    [Here](https://stackoverflow.com/q/58534445/9758194) you can find some information on what it does. – JvdV Nov 21 '19 at 16:54

1 Answers1

0

I was wondering the same thing. It turns out that it's limiting the result of a formula to a single value, when the formula is capable of returning an array.

This looks like a good explanation: https://www.ablebits.com/office-addins-blog/excel-implicit-intersection/

As for your query about wanting a formula to return "Na()" - I think I'd need to know a bit more about the specific formula before I can help you to debug it and get he behaviour you want.

Stéphane English
  • 1