I'm using the following formula, but it's returning #N/A:

=INDEX('File X'!$F$1:$F$2000,MATCH(1,($B5='File X'!$A$1:$A$2000)*(S$4='File
X'!$G$1:$G$2000),0)

However, if I replace the S$4 with "7710", it returns the correct value.
The number in S4 is 7710. Why won't the formula recognize the cell
reference, but if I type the contents of the cell manually, it works? I
double-checked to make sure nothing was entered as text.

Hi,
is 7710 a number typed or imported, check if you don't have blank spaces in
one of the two cells you are comparing

"jeikenberry" wrote:

I'm using the following formula, but it's returning #N/A:

=INDEX('File X'!$F$1:$F$2000,MATCH(1,($B5='File X'!$A$1:$A$2000)*(S$4='File
X'!$G$1:$G$2000),0)

However, if I replace the S$4 with "7710", it returns the correct value.
The number in S4 is 7710. Why won't the formula recognize the cell
reference, but if I type the contents of the cell manually, it works? I
double-checked to make sure nothing was entered as text.

I typed it in myself. I did double-check to make sure there are no spaces.

"Eduardo" wrote:

Hi,
is 7710 a number typed or imported, check if you don't have blank spaces in
one of the two cells you are comparing

"jeikenberry" wrote:

I'm using the following formula, but it's returning #N/A:

=INDEX('File X'!$F$1:$F$2000,MATCH(1,($B5='File X'!$A$1:$A$2000)*(S$4='File
X'!$G$1:$G$2000),0)

However, if I replace the S$4 with "7710", it returns the correct value.
The number in S4 is 7710. Why won't the formula recognize the cell
reference, but if I type the contents of the cell manually, it works? I
double-checked to make sure nothing was entered as text.

Check whether S4 cell is formatted as text . If so either change that to
general format and re-enter the number or modify the formula as

(--S$4='File X'!$G$1:$G$2000)


--
Jacob (MVP - Excel)


"jeikenberry" wrote:

I typed it in myself. I did double-check to make sure there are no spaces.

"Eduardo" wrote:

Hi,
is 7710 a number typed or imported, check if you don't have blank spaces in
one of the two cells you are comparing

"jeikenberry" wrote:

I'm using the following formula, but it's returning #N/A:

=INDEX('File X'!$F$1:$F$2000,MATCH(1,($B5='File X'!$A$1:$A$2000)*(S$4='File
X'!$G$1:$G$2000),0)

However, if I replace the S$4 with "7710", it returns the correct value.
The number in S4 is 7710. Why won't the formula recognize the cell
reference, but if I type the contents of the cell manually, it works? I
double-checked to make sure nothing was entered as text.