#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)) |
#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 |
#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 . |
#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 . |
#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 |
#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 . |
All times are GMT +1. The time now is 02:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com