| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
ISNA with Index
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ISNA with Index
=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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ISNA with Index
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ISNA with Index
=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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ISNA with Index
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ISNA with Index
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ISNA with Index
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) "Dave Peterson" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ISNA with Index
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. pgarcia wrote: 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) "Dave Peterson" wrote: 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 -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ISNA with Index
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 "Dave Peterson" wrote: 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. pgarcia wrote: 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) "Dave Peterson" wrote: 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF(ISNA.... | Excel Discussion (Misc queries) | |||
| IF ISNA Help | Excel Discussion (Misc queries) | |||
| ISNA help | Excel Worksheet Functions | |||
| Using ISNA with OR | Excel Worksheet Functions | |||
| ISNA | Excel Worksheet Functions | |||
Linear Mode
