Vlookup error when lookup value cell has been populated with formula

Question

I have the following formula which is producing a #N/A error:

=VLOOKUP(N8,Lookups!J5:L2772,3,FALSE)

The formula in N8 is:

=LEFT(M8,4)

And the value in N8 will be the first part of a UK postcode (e.g. N1, W1A, SW1Y, TF1, SO26)

I've tried changing the vlookup to an index and match formula but cannot get that to work either.

Answer 1

Your formula returns 4 characters. =LEFT(M8,FIND(" ",M8)-1) will return the characters up to the first space (and excluding the space).

TRIM(N8) would also remove trailing spaces if you plan to keep your formula as it was. In that case =VLOOKUP(TRIM(N8),Lookups!J5:L2772,3,FALSE) would also work, but this is less likely to throw an error:

(=VLOOKUP(LEFT(M8,FIND(" ",M8)-1),'Lookups'!J5:L2772,3,0))