# #N/A situation

1. ## #N/A situation

Hi,

In my Worksheet [Col c] is have the following code which performs the required Function set for it.
But when the rows do not contain the right data it end up with #N/A
now I know I need to wrap this array within a code which will remove/replace the #N/A
So the question is how do I wrap the code within the function required.

=INDEX(\$R\$21:\$R\$37,MATCH(G29,INDEX(\$R\$21:\$R\$37,0,1))+ (G29<>VLOOKUP(G29,\$R\$21:\$R\$37,1)),\$F\$1)

regards

Barry  Register To Reply

2. Hi Barry,

I'm a little confused - is it code (VBA) that's returning the value or a typical spreadsheet formula? If it's the later, you could use...

=IF(ISERROR(INDEX(\$R\$21:\$R\$37,MATCH(G29,INDEX(\$R\$21:\$R\$37,0,1))+(G29<>VLOOKUP(G29,\$R\$21:\$R\$37,1)),\$F\$1)),"",INDEX(\$R\$21:\$R\$37,MATCH(G29,INDEX(\$R\$21:\$R\$37,0,1))+(G29<>VLOOKUP(G29,\$R\$21:\$R\$37,1))))

...to return a blank if there's an error or perform the formula if there's not.

HTH

Robert  Register To Reply

3. Hi,
No not VB Code just a Function
Tried the suggested but that unfortuately did not work
Any further Suggestions?

regards

Barry  Register To Reply

4. What I sent was an exact copy of what is in the cell which is returning the #N/A

regards

Barry  Register To Reply

5. Hi Barry,

Strange as it worked for me - is calculation on automatic?

If it is, could you zip the file and attach it (or part thereof) as it makes it much easier if I have actual file to look at.

Kind regards,

Robert  Register To Reply

6. Thanks It was just a set up [address] error on my behalf,
many thanks

Barry  Register To Reply