VLookup Error - Sometimes works, sometimes doesn't

[Amackin]

Hi All! Spent about 3 hours working on a spreadsheet here and officially at the end of my tether, so wondering if someone more talented can help me out!

I have a Vlookup which is working absolutely fine half of the time, and randomly spits out a #N/A the other half. Lots of other formulas happening in the same cell, but I've narrowed the error down to a single VLookup

Essentially, the formula looks at a cell containing the first half of a postcode (for example, G61), looks for that value in a table on a different tab, and results in the corresponding cost (for example, £40)

=VLOOKUP(AC2,'Postcode Rates'!$C$3:$W$12281,2,FALSE)

The thing I'm struggling to understand is, the formula works absolutely fine sometimes, and gives me an error the rest of the time

In cell AC2, the postcode is not typed in, but is a formula to another different tab

=Import!X2

Bizarrely, if I take the formula in AC2 which gives me the result 'G61', and overwrite it manually with 'G61', it works! So I don't know if the error is with the Vlookup or the formula in AC2

No amount of formatting cells seems to work. Even if I overwrite the cell and paste value, it doesn't work. It only works if I manually type the result in.

Officially at the limits of my Excel abilities, can anyone please help?

Really appreciate it, thank you

 

[Fluff]

One possibility is that the values that don't work have leading/trailing spaces.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...times-works-sometimes-doesnt.html#post5101673

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Amackin]

Apologies, have to admin I gave the rules only a cursory glance, sorry!

Turns out you were completely right about the issue. The Vlookup would fail as it was searching for "G61 " as opposed to "G61"

Added a TRIM formula in and it all works fine now. Thank you so much

 

[Fluff]

Glad it's sorted & thanks for the feedback