Formula appears correct, result populated in cell is #N/A

[tnobbs]

HELP I'm confused...Formula Result = AT327M80 which is what I expect to see, however the cell gets populated with #N/A
=IF(O13="XYZ",(INDEX(Networks!G$2:G$1062,MATCH(1,('Input Labor'!E13=Networks!A$2:A$1062)*('Input Labor'!Q13=Networks!H$2:H$1062),0))),(D13&B13&O13))

 

[joeu2004]

=IF(O13="XYZ",(INDEX(Networks!G$2:G$1062,MATCH(1,('Input Labor'!E13=Networks!A$2:A$1062)*('Input Labor'!Q13=Networks!H$2:H$1062),0))),(D13&B13&O13))

Did you array-enter the formula: press ctrl+shift+Enter instead of just Enter?

I suspect that the fx feature treats all formulas as array-entered formulas. (Confirmed.)

But the Excel calculation engine needs help to distinguish array expressions (intended to return an array of values) from implicit intersection expressions (intended to return a single value from an array).

Example:

Book1
	A	B	C	D	E	F	G
1
2	#N/A	c3		d2		f2
3			c3		d2		f2
4
5
6
7
8
9
10
Sheet1

Select A2 and B2 or hover the cursor over each cell to see the formulas. They are the same formula. The formula in A2 was normally-entered; just pressed Enter. The curly-braces around the formula in B2 indicate that it was array-entered; pressed ctrl+shift+Enter.

BTW, it might improve readability if you remove unnecessary parentheses, highlighted in red in the quoted text above.

 

[joeu2004]

Did you array-enter the formula: press ctrl+shift+Enter instead of just Enter?

To clarify, select only one cell and array-enter the formula.

If you want to populate a column with similar formulas, then copy the first cell and paste into the other cells below.

Do __not__ select a column of cells and array-enter the formula. That creates a multi-cell array formula, which is unnecessary and which causes complications for editing later.