Hello all,
Can you add a ISNA in a index match formula? I try to put it in the formula
but it did not like it.

{=INDEX('101408'!$T$2:$T$2491,MATCH(1,(M2='101408' !$R$2:$R$2491)*(I2='101408'!$S$2:$S$2491),0))}

Thanks

=if(isna(match(that long expression)),"no match",index(your formula here))

pgarcia wrote:

Hello all,
Can you add a ISNA in a index match formula? I try to put it in the formula
but it did not like it.

{=INDEX('101408'!$T$2:$T$2491,MATCH(1,(M2='101408' !$R$2:$R$2491)*(I2='101408'!$S$2:$S$2491),0))}

Thanks

--

Dave Peterson

I'm sorry, hows that? Please see below, I'm not sure on the "long expression".

=if(isna(match(that long expression)),"no
match",INDEX('101408'!$T$2:$T$2491,MATCH(1,(M2='10 1408'!$R$2:$R$2491)*(I2='101408'!$S$2:$S$2491),0))

Thanks

"Dave Peterson" wrote:

=if(isna(match(that long expression)),"no match",index(your formula here))

pgarcia wrote:

Hello all,
Can you add a ISNA in a index match formula? I try to put it in the formula
but it did not like it.

{=INDEX('101408'!$T$2:$T$2491,MATCH(1,(M2='101408' !$R$2:$R$2491)*(I2='101408'!$S$2:$S$2491),0))}

Thanks

--

Dave Peterson

=if(isna(MATCH(1,(M2='101408'!$R$2:$R$2491)*(I2='1 01408'!$S$2:$S$2491),0)),
"no match",
INDEX('101408'!$T$2:$T$2491,
MATCH(1,(M2='101408'!$R$2:$R$2491)*(I2='101408'!$S $2:$S$2491),0)))


pgarcia wrote:

I'm sorry, hows that? Please see below, I'm not sure on the "long expression".

=if(isna(match(that long expression)),"no
match",INDEX('101408'!$T$2:$T$2491,MATCH(1,(M2='10 1408'!$R$2:$R$2491)*(I2='101408'!$S$2:$S$2491),0))

Thanks

"Dave Peterson" wrote:

=if(isna(match(that long expression)),"no match",index(your formula here))

pgarcia wrote:

Hello all,
Can you add a ISNA in a index match formula? I try to put it in the formula
but it did not like it.

{=INDEX('101408'!$T$2:$T$2491,MATCH(1,(M2='101408' !$R$2:$R$2491)*(I2='101408'!$S$2:$S$2491),0))}

Thanks

--

Dave Peterson


--

Dave Peterson

Wow, ok, that works. Could I ask you another question? I have swiched to
Excel 2007 here at work, why would my sumproduct formula stop working? I have
to click on F2 when I'm in the cell and it works. I have everthing turn on.
Any ideas?

=SUMPRODUCT(--(H18:H1002),--(H18:H100<7))

"Dave Peterson" wrote:

=if(isna(MATCH(1,(M2='101408'!$R$2:$R$2491)*(I2='1 01408'!$S$2:$S$2491),0)),
"no match",
INDEX('101408'!$T$2:$T$2491,
MATCH(1,(M2='101408'!$R$2:$R$2491)*(I2='101408'!$S $2:$S$2491),0)))


pgarcia wrote:

I'm sorry, hows that? Please see below, I'm not sure on the "long expression".

=if(isna(match(that long expression)),"no
match",INDEX('101408'!$T$2:$T$2491,MATCH(1,(M2='10 1408'!$R$2:$R$2491)*(I2='101408'!$S$2:$S$2491),0))

Thanks

"Dave Peterson" wrote:

=if(isna(match(that long expression)),"no match",index(your formula here))

pgarcia wrote:

Hello all,
Can you add a ISNA in a index match formula? I try to put it in the formula
but it did not like it.

{=INDEX('101408'!$T$2:$T$2491,MATCH(1,(M2='101408' !$R$2:$R$2491)*(I2='101408'!$S$2:$S$2491),0))}

Thanks

--

Dave Peterson


--

Dave Peterson

It kind of sounds like you have calculation in manual mode.

Office button|excel options|
(and I don't remember the rest!)
Look for the calculation setting.





pgarcia wrote:

Wow, ok, that works. Could I ask you another question? I have swiched to
Excel 2007 here at work, why would my sumproduct formula stop working? I have
to click on F2 when I'm in the cell and it works. I have everthing turn on.
Any ideas?

=SUMPRODUCT(--(H18:H1002),--(H18:H100<7))

"Dave Peterson" wrote:

=if(isna(MATCH(1,(M2='101408'!$R$2:$R$2491)*(I2='1 01408'!$S$2:$S$2491),0)),
"no match",
INDEX('101408'!$T$2:$T$2491,
MATCH(1,(M2='101408'!$R$2:$R$2491)*(I2='101408'!$S $2:$S$2491),0)))


pgarcia wrote:

I'm sorry, hows that? Please see below, I'm not sure on the "long expression".

=if(isna(match(that long expression)),"no
match",INDEX('101408'!$T$2:$T$2491,MATCH(1,(M2='10 1408'!$R$2:$R$2491)*(I2='101408'!$S$2:$S$2491),0))

Thanks

"Dave Peterson" wrote:

=if(isna(match(that long expression)),"no match",index(your formula here))

pgarcia wrote:

Hello all,
Can you add a ISNA in a index match formula? I try to put it in the formula
but it did not like it.

{=INDEX('101408'!$T$2:$T$2491,MATCH(1,(M2='101408' !$R$2:$R$2491)*(I2='101408'!$S$2:$S$2491),0))}

Thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson