#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 120
Default 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?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default 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?



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,624
Default 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?

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 120
Default 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?




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default 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?






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
copy of excel file not showing formulal/function in the function b oaallam Excel Discussion (Misc queries) 4 September 6th 07 01:20 PM
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) [email protected] Excel Worksheet Functions 0 September 5th 06 03:44 PM
Offset function with nested match function not finding host ss. MKunert Excel Worksheet Functions 1 March 21st 06 11:46 PM
Emulate Index/Match combo function w/ VBA custom function Spencer Hutton Excel Worksheet Functions 2 May 2nd 05 05:26 PM
Nested IF Function, Date Comparing, and NetworkDays Function carl Excel Worksheet Functions 2 December 29th 04 10:57 PM


All times are GMT +1. The time now is 11:32 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"