Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#NA error on a INDEX formular
Does anybody know why I am getting #NA with this formula. It was working OK
awhile ago???? =INDEX($B$2:$BJ$402,MATCH(B411,$A$2:$A$402,0),MATC H(B410,$B$1:$BJ$1,0)) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
#NA error on a INDEX formular
Put these portions of your formula in two other cells:
=MATCH(B411,$A$2:$A$402,0) =MATCH(B410,$B$1:$BJ$1,0) One or both will return that #N/A error. You'll have to find out why there's a mismatch (spelling error, too many spaces, ...). Pas wrote: Does anybody know why I am getting #NA with this formula. It was working OK awhile ago???? =INDEX($B$2:$BJ$402,MATCH(B411,$A$2:$A$402,0),MATC H(B410,$B$1:$BJ$1,0)) -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
#NA error on a INDEX formular
Cheers Dave, I'll try that and let you know how I get on.
"Dave Peterson" wrote: Put these portions of your formula in two other cells: =MATCH(B411,$A$2:$A$402,0) =MATCH(B410,$B$1:$BJ$1,0) One or both will return that #N/A error. You'll have to find out why there's a mismatch (spelling error, too many spaces, ...). Pas wrote: Does anybody know why I am getting #NA with this formula. It was working OK awhile ago???? =INDEX($B$2:$BJ$402,MATCH(B411,$A$2:$A$402,0),MATC H(B410,$B$1:$BJ$1,0)) -- Dave Peterson . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
#NA error on a INDEX formular
Yes the second formula results in #NA. I have a textbox linked to cell "B410"
, which has a formula. When I clear the formula on that cell it works fine. For some weird reason it's giving #NA because the fact that the cell has a formula??? Any ideas? "Pas" wrote: Cheers Dave, I'll try that and let you know how I get on. "Dave Peterson" wrote: Put these portions of your formula in two other cells: =MATCH(B411,$A$2:$A$402,0) =MATCH(B410,$B$1:$BJ$1,0) One or both will return that #N/A error. You'll have to find out why there's a mismatch (spelling error, too many spaces, ...). Pas wrote: Does anybody know why I am getting #NA with this formula. It was working OK awhile ago???? =INDEX($B$2:$BJ$402,MATCH(B411,$A$2:$A$402,0),MATC H(B410,$B$1:$BJ$1,0)) -- Dave Peterson . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
#NA error on a INDEX formular
Nope.
It's giving you the error because the value in that textbox doesn't match any of the cells in B1:BJ1. == Are the values you're matching on digits? If yes, then try: =MATCH(--B410,$B$1:$BJ$1,0) The -- stuff will coerce any text number to a real number. The first minus converts the text number to a number (but the opposite sign). The second changes the sign back but keeps the number. Pas wrote: Yes the second formula results in #NA. I have a textbox linked to cell "B410" , which has a formula. When I clear the formula on that cell it works fine. For some weird reason it's giving #NA because the fact that the cell has a formula??? Any ideas? "Pas" wrote: Cheers Dave, I'll try that and let you know how I get on. "Dave Peterson" wrote: Put these portions of your formula in two other cells: =MATCH(B411,$A$2:$A$402,0) =MATCH(B410,$B$1:$BJ$1,0) One or both will return that #N/A error. You'll have to find out why there's a mismatch (spelling error, too many spaces, ...). Pas wrote: Does anybody know why I am getting #NA with this formula. It was working OK awhile ago???? =INDEX($B$2:$BJ$402,MATCH(B411,$A$2:$A$402,0),MATC H(B410,$B$1:$BJ$1,0)) -- Dave Peterson . -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
#NA error on a INDEX formular
Thanks Dave,
That did the trick, I did have some minus numbers Many thanks "Dave Peterson" wrote: Nope. It's giving you the error because the value in that textbox doesn't match any of the cells in B1:BJ1. == Are the values you're matching on digits? If yes, then try: =MATCH(--B410,$B$1:$BJ$1,0) The -- stuff will coerce any text number to a real number. The first minus converts the text number to a number (but the opposite sign). The second changes the sign back but keeps the number. Pas wrote: Yes the second formula results in #NA. I have a textbox linked to cell "B410" , which has a formula. When I clear the formula on that cell it works fine. For some weird reason it's giving #NA because the fact that the cell has a formula??? Any ideas? "Pas" wrote: Cheers Dave, I'll try that and let you know how I get on. "Dave Peterson" wrote: Put these portions of your formula in two other cells: =MATCH(B411,$A$2:$A$402,0) =MATCH(B410,$B$1:$BJ$1,0) One or both will return that #N/A error. You'll have to find out why there's a mismatch (spelling error, too many spaces, ...). Pas wrote: Does anybody know why I am getting #NA with this formula. It was working OK awhile ago???? =INDEX($B$2:$BJ$402,MATCH(B411,$A$2:$A$402,0),MATC H(B410,$B$1:$BJ$1,0)) -- Dave Peterson . -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX error | Excel Worksheet Functions | |||
INDEX/MATCH #REF! error | Excel Discussion (Misc queries) | |||
index error | Excel Discussion (Misc queries) | |||
INDEX error | Excel Worksheet Functions | |||
INDEX and #REF error | Excel Discussion (Misc queries) |