I have run some vlookups some of which resulted in an #N/A result. I converted these to fixed values by copy->specialPaste Values.
I would like to replace all the #N/A which are now text with zero (0) but find does not seem to find/replace them (search key not found).
Is there a trick to having the find dialogue find these?
VLOOKUP can often return #NA. I normally deal with this in the lookup formula, e.g. =IF(ISNA(VLOOKUP(A2,Sheet2.$A$2:$B$15,2,0)),"",VLOOKUP(A2,Sheet2.$A$2:$B$15,2,0)) which returns a blank cell. To get a 0 instead of blank replace the "" with 0
This can be shorter with:
=IFNA(VLOOKUP(A2;Sheet2.$A$2:$B$15;2;0);"")
Thank You so much for this tip. It worked perfectly.
Thank you so much for response. I was aware of this and use it often, but in this case - a simple one-off query - I was a bit lazy and did not use the ISNA function.