Function Help
 

I keep getting an error with a formula that I am working with. When I build
the formula through the Function Wizard I can see the result but when I click
on OK I get a Value Error.

Here is the formula

=IF(ISNA(INDEX($C$37:$C$118,MATCH($A9&C$7,$A$37:$A $98&$B$37:$B$118,0))),0,INDEX($C$37:$C$118,MATCH($ A9&C$7,$A$37:$A$118&$B$37:$B$118,0)))

What am I missing?

Function Help
 

You need to enter the formula with

ctrl + shift enter

as opposed to enter only

--


Regards,


Peo Sjoblom




"Erika" wrote in message
...
I keep getting an error with a formula that I am working with. When I
build
the formula through the Function Wizard I can see the result but when I
click
on OK I get a Value Error.

Here is the formula

=IF(ISNA(INDEX($C$37:$C$118,MATCH($A9&C$7,$A$37:$A $98&$B$37:$B$118,0))),0,INDEX($C$37:$C$118,MATCH($ A9&C$7,$A$37:$A$118&$B$37:$B$118,0)))

What am I missing?

Function Help
 

First, your ranges all need to be the same size.

Second, you don't need the INDEX() function within the ISNA() function.

Third, your need to array-enter the function, with CTRL-SHIFT-ENTER or
CMD-RETURN. Try:

=IF(ISNA(MATCH($A9 & C$7, $A$37:$A$118 & $B$37:$B$118, 0)), 0,
INDEX($C$37:$C$118, MATCH($A9 & C$7,$A$37:$A$118 & $B$37:$B$118, 0)))


In article ,
Erika wrote:

I keep getting an error with a formula that I am working with. When I build
the formula through the Function Wizard I can see the result but when I click
on OK I get a Value Error.

Here is the formula

=IF(ISNA(INDEX($C$37:$C$118,MATCH($A9&C$7,$A$37:$A $98&$B$37:$B$118,0))),0,INDE
X($C$37:$C$118,MATCH($A9&C$7,$A$37:$A$118&$B$37:$B $118,0)))

What am I missing?

Function Help
 

That worked, that work perfectly. I hate to be a pain but what is the
difference between enter and ctrl + shift enter

Thank you so much for your help!

"Peo Sjoblom" wrote:

You need to enter the formula with

ctrl + shift enter

as opposed to enter only

--


Regards,


Peo Sjoblom




"Erika" wrote in message
...
I keep getting an error with a formula that I am working with. When I
build
the formula through the Function Wizard I can see the result but when I
click
on OK I get a Value Error.

Here is the formula

=IF(ISNA(INDEX($C$37:$C$118,MATCH($A9&C$7,$A$37:$A $98&$B$37:$B$118,0))),0,INDEX($C$37:$C$118,MATCH($ A9&C$7,$A$37:$A$118&$B$37:$B$118,0)))

What am I missing?

Function Help
 

It's an array formula, you can search help for array formula

You might want to check JE McGimpsey's audit of your original formula given
that your ranges were unequal in size and you have an extra function call


--


Regards,


Peo Sjoblom



"Erika" wrote in message
...
That worked, that work perfectly. I hate to be a pain but what is the
difference between enter and ctrl + shift enter

Thank you so much for your help!

"Peo Sjoblom" wrote:

You need to enter the formula with

ctrl + shift enter

as opposed to enter only

--


Regards,


Peo Sjoblom




"Erika" wrote in message
...
I keep getting an error with a formula that I am working with. When I
build
the formula through the Function Wizard I can see the result but when I
click
on OK I get a Value Error.

Here is the formula

=IF(ISNA(INDEX($C$37:$C$118,MATCH($A9&C$7,$A$37:$A $98&$B$37:$B$118,0))),0,INDEX($C$37:$C$118,MATCH($ A9&C$7,$A$37:$A$118&$B$37:$B$118,0)))

What am I missing?