Function Help

[Guest]

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?

 

[Peo Sjoblom]

You need to enter the formula with

ctrl + shift enter

as opposed to enter only

--


Regards,


Peo Sjoblom

 

[JE McGimpsey]

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)))

 

[Guest]

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]

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