Prev: Pivot Table Comparison
Next: Pull info from one excel file to another depending on values of O.
From: jeikenberry on 28 Apr 2010 13:06
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.
From: Eduardo on 28 Apr 2010 13:12
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.
From: jeikenberry on 28 Apr 2010 13:36
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.
From: Jacob Skaria on 29 Apr 2010 01:25
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.
 | 
Pages: 1
Prev: Pivot Table Comparison
Next: Pull info from one excel file to another depending on values of O.