CODEWITHSUNDEEP
excelexcel-formulaformulavlookup
Nene
Nene

Reputation: 55

vlookup error when lookup value cell has been populated with formula

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.

Any help is greatly appreciated.

Many thanks :)

formulas

values

postcode lookup

Upvotes: 0

Views: 437

Answers (1)

P.b
P.b

Reputation: 11653

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

Upvotes: 0

Related Questions