Using a full postcode return the town value from a list of shorter pre fix's

Hi,

I have a list of postcodes which in the example is column A, and I want to return the town value from column I, where the first 4 digitis of the full postode in Column A are found in the pre fix postcode list of column G. The full sheet I have has around 600 full postcodes, however the example attached only has 6, and with the info on it should only retrun 1 value

Thanks

Hi,

Thanks for your reply. That seems to work for a couple of examples but not for the whole lot. Out of the full list only 6 are coming back with the retrun value with the rest remaining NA. I've updated the example document so you can see what I mean

Thanks

Hi,

Got it, thanks for your help :-)

Hi,

I've actually have found a problem where the full postcode has an initial double digit number, ie LN32, where it then doesnt pick up the corresponding town from the table? Everything in the standard format ie DE6 is fine

Hi,

=LOOKUP(10^99,SEARCH(SUBSTITUTE($G$2:$G$1423," ",""),SUBSTITUTE(A2," ","")),$I$2:$I$1423) works perferctly

Thanks