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
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