Text instead of #N/A

[danesrood]

Hi all from a rather gloomy Southern England.

I have a rather complicated formula that basically has a couple of If statements and then a lookup against a range named MyPostCodes.

If the English PostCode in G3 is not in the MyPostCodes range I get the error #N/A.

Is it possible to have the word "Transfer" instead


=IF(I3="Barnstaple","Barnstaple",(IF(G3="","No Post Code",(IFERROR(VLOOKUP(LEFT(G3,FIND(" ",G3)+1),MyPostCodes,2,FALSE),VLOOKUP(LEFT(G3,FIND(" ",G3)-1),MyPostCodes,2,FALSE))))))

As ever my sincere thanks for any help that you can provide

[Yongle]

Just wrap this around your original formula =IFERROR(orig formula,"TRANSFER")

so formula becomes:
=IFERROR(IF(I3="Barnstaple","Barnstaple",(IF(G3="","No Post Code",(IFERROR(VLOOKUP(LEFT(G3,FIND(" ",G3)+1),MyPostCodes,2,FALSE),VLOOKUP(LEFT(G3,FIND(" ",G3)-1),MyPostCodes,2,FALSE)))))),"TRANSFER")

should work

[danesrood]

Yongle

Thank you it works a treat.