Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |