PDA

View Full Version : [SOLVED] Text instead of #N/A



danesrood
05-15-2015, 05:22 AM
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
05-15-2015, 05:31 AM
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
05-15-2015, 05:59 AM
Yongle

Thank you it works a treat.