Why did Excel auto added the “@” sign?

[vchinthakindi]

When I write below formula into excel its auto adding @ symbol. How to avoid this?

I tried using set cell value and also placed value in collection, and tried write collection to excel as well

Expected:

=IF(ISNUMBER(SEARCH($N$1,(INDEX('Void Checks'!L:L,MATCH('Printed Checks'!B2&'Printed Checks'!D2,'Void Checks'!A:A&'Void Checks'!B:B,0))))),$N$1,IF(ISNUMBER(SEARCH($O$1,(INDEX('Cashed Checks'!F:F,MATCH('Printed Checks'!B2&'Printed Checks'!D2,'Cashed Checks'!A:A&'Cashed Checks'!B:B,0))))),$O$1,$P$1))

Result: (with @ symbol)

=IF(ISNUMBER(SEARCH($N$1,(INDEX('Void Checks'!L:L,MATCH('Printed Checks'!B2&'Printed Checks'!D2,@'Void Checks'!A:A&'Void Checks'!B:B,0))))),$N$1,IF(ISNUMBER(SEARCH($O$1,(INDEX('Cashed Checks'!F:F,MATCH('Printed Checks'!B2&'Printed Checks'!D2,@'Cashed Checks'!A:A&'Cashed Checks'!B:B,0))))),$O$1,$P$1))

[Parthiban_Viatris24]

The @ symbol is automatically added by Excel when working with structured references or arrays in certain versions of Excel, particularly when using dynamic arrays and tables. The @ symbol is used to reference a single value in an array or table. However, if you want to avoid the @ symbol being added to your formula, you can use the following approaches:

1. Using Array Formula (Legacy Array Formulas)

• You can enter the formula as an array formula by pressing Ctrl + Shift + Enter instead of just Enter when you input the formula. Excel will treat the formula as an array formula, which should prevent the @ symbol from being added automatically.

• How to do it:

  1. Select the cell where you want to enter the formula.
  2. Paste the formula:
     =IF(ISNUMBER(SEARCH($N$1,(INDEX('Void Checks'!L:L,MATCH('Printed Checks'!B2&'Printed Checks'!D2,'Void Checks'!A:A&'Void Checks'!B:B,0))))),$N$1,IF(ISNUMBER(SEARCH($O$1,(INDEX('Cashed Checks'!F:F,MATCH('Printed Checks'!B2&'Printed Checks'!D2,'Cashed Checks'!A:A&'Cashed Checks'!B:B,0))))),$O$1,$P$1))
  3. Instead of pressing Enter, press Ctrl + Shift + Enter. This will enter the formula as an array formula, and Excel will surround the formula with curly braces {}.

2. Using Explicit Array Syntax

• For versions of Excel that support dynamic arrays (Excel 365 or Excel 2019), you can explicitly tell Excel to treat the formula as an array without adding @ by using the array syntax.
• Simply make sure the formula references are correct and that you are not referencing individual cells but entire ranges or columns.

3. Using INDIRECT Function (Alternative Approach)

• As a workaround, you can use the INDIRECT function to avoid Excel interpreting part of the formula as a table reference.

• Example:

  =IF(ISNUMBER(SEARCH($N$1,(INDEX('Void Checks'!L:L,MATCH('Printed Checks'!B2&'Printed Checks'!D2,INDIRECT("'Void Checks'!A:A")&INDIRECT("'Void Checks'!B:B"),0))))),$N$1,IF(ISNUMBER(SEARCH($O$1,(INDEX('Cashed Checks'!F:F,MATCH('Printed Checks'!B2&'Printed Checks'!D2,INDIRECT("'Cashed Checks'!A:A")&INDIRECT("'Cashed Checks'!B:B"),0))))),$O$1,$P$1))

• The INDIRECT function converts the reference to a text string, which helps avoid the automatic @ symbol insertion.

By following these methods, you should be able to prevent Excel from automatically adding the @ symbol to your formula. If one approach doesn't work, try another, depending on your specific version of Excel and its behavior.