ISNA with Index

  • Thread starter Thread starter pgarcia
  • Start date Start date
P

pgarcia

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
 
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='101408'!$R$2:$R$2491)*(I2='101408'!$S$2:$S$2491),0))

Thanks
 
=if(isna(MATCH(1,(M2='101408'!$R$2:$R$2491)*(I2='101408'!$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)))
 
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:H100>2),--(H18:H100<7))
 
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.




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:H100>2),--(H18:H100<7))
Click to expand...
 
I looks to be on to me - Workbook Calculation is set to "Automatic". Could it
be something else? A VB code or some other thing the option panel. Would it
make difference if the spread sheet is in "Compatibility Mode"? (I'm going to
check that right now)
 
What does "stop working" mean?

Does that mean that you see a result, but it's not right (that was my
calculation setting guess)?

Or does it mean that you see the formula--not what the formula should evaluate
to?

If that's the case, then you can format the offending cell as General (or
anything but Text), then reenter the formula. A quick way to get lots of cells
is to select the offending cells and then do:

Edit|replace (or whatever it is in xl2007)
what: = (equal sign)
with: =
replace all

In fact, if you see a result, just an incorrect result, try this edit|replace
technique against all the cells in the worksheet.
I looks to be on to me - Workbook Calculation is set to "Automatic". Could it
be something else? A VB code or some other thing the option panel. Would it
make difference if the spread sheet is in "Compatibility Mode"? (I'm going to
check that right now)
Click to expand...
 
Ya, I'm sure what is going on, but I put it in as a macro. At less what way I
don't have to worry about it.
Thanks
 
Back
Top