+ Reply to Thread
Results 1 to 9 of 9

VLookup Error - Sometimes works, sometimes doesn't

  1. 04-12-2019, 12:56 PM #1
    Amackin
    Amackin is offline
    Registered User
    Join Date
    04-12-2019
    Location
    UK
    MS-Off Ver
    2016
    Posts
    5

    VLookup Error - Sometimes works, sometimes doesn't

    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
    Reply With QuoteRegister To Reply
  2. 04-12-2019, 12:59 PM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: VLookup Error - Sometimes works, sometimes doesn't

    Welcome to the forum!

    Hard to say without seeing the workbook, sorry.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 04-12-2019, 01:02 PM #3
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: VLookup Error - Sometimes works, sometimes doesn't

    Sorry - forgot the instructions!

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Reply With QuoteRegister To Reply
  4. 04-12-2019, 04:14 PM #4
    Amackin
    Amackin is offline
    Registered User
    Join Date
    04-12-2019
    Location
    UK
    MS-Off Ver
    2016
    Posts
    5

    Re: VLookup Error - Sometimes works, sometimes doesn't

    Apologies, tried to upload the workbook when I first posted but couldnt figure out how!

    Found a solution eventually!

    The postcodes had spaces in certain instances. So the Vlookup would fail because it was looking for "G61 " as opposed to "G61"

    Changed the formula in the original cell to the below and it all worked

    =TRIM(Import!Q2)
    Reply With QuoteRegister To Reply
  5. 04-12-2019, 04:41 PM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: VLookup Error - Sometimes works, sometimes doesn't

    Good news!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  6. 04-12-2019, 05:13 PM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: VLookup Error - Sometimes works, sometimes doesn't

    Administrative Note:

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.
    Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. As you are new here, I have added the link for you this time.)

    https://www.mrexcel.com/forum/excel-...es-doesnt.html
    Last edited by AliGW; 04-12-2019 at 05:19 PM.
    Reply With QuoteRegister To Reply
  7. 04-12-2019, 05:15 PM #7
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: VLookup Error - Sometimes works, sometimes doesn't

    Removed by FR.

    Ali beat me to it.
    Dave
    Reply With QuoteRegister To Reply
  8. 04-12-2019, 05:16 PM #8
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: VLookup Error - Sometimes works, sometimes doesn't

    I was just about to add the link - thanks, Dave.
    Reply With QuoteRegister To Reply
  9. 04-12-2019, 05:18 PM #9
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: VLookup Error - Sometimes works, sometimes doesn't

    And I just removed it. LOL

    https://www.mrexcel.com/forum/excel-...es-doesnt.html
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. WorksheetFunction.Vlookup Runtime Error 1004, But works in Excel Sheet.
    By hansaaa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-28-2018, 11:52 AM
  2. [SOLVED] VLOOKUP works for some records, but most give #n/a error
    By avib in forum Excel General
    Replies: 9
    Last Post: 07-27-2015, 10:54 AM
  3. Macro doesn't work on external source based pivot (on internal works correctly). Error 400
    By shyha in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-17-2015, 09:06 AM
  4. [SOLVED] .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.Clear
    By Doc.AElstein in forum Excel - New Users/Basics
    Replies: 28
    Last Post: 03-16-2015, 12:55 PM
  5. [SOLVED] vLookup works, then doesn't work- what's causing the inconsistency?
    By ScotyB in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-16-2012, 09:56 PM
  6. [SOLVED] #VALUE! error: vlookup works in Excel 2000 but not 2003
    By Nick Ersdown in forum Excel General
    Replies: 6
    Last Post: 11-25-2005, 08:30 AM
  7. it works, it doesn work, its works....and so on.
    By Naz in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-30-2005, 09:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1