+ Reply to Thread
Results 1 to 10 of 10

VLOOKUP - #N/A - (video inside)

  1. 04-26-2015, 08:32 AM #1
    domgilberto
    domgilberto is offline
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    358

    VLOOKUP - #N/A - (video inside)

    I know the majority of you find it easier to see a spread-sheet example, but I've decided to make this 2 minute video explaining the issue instead (as I am utterly convinced there is a quick explanation to what is going on here).

    It's driving me bat-crazy. Why the VLOOKUP won't work I have no idea. The data points are identical in length and the date ranges are == to each other :s!?

    Please spare 2 minutes to watch this video: http://screencast.com/t/Ule7fk8g7F8v

    Thank you kindly to all of you who can help in anyway shape or form!
    Reply With QuoteRegister To Reply
  2. 04-26-2015, 09:19 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: VLOOKUP - #N/A - (video inside)

    I suspect that this is down to Excels way of counting. Put your lookup value AND the target cell inside a ROUND function and it works.
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 04-26-2015, 09:25 AM #3
    domgilberto
    domgilberto is offline
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    358

    Re: VLOOKUP - #N/A - (video inside)

    Hmmm good shout - I tried this with no luck either!
    Reply With QuoteRegister To Reply
  4. 04-26-2015, 09:40 AM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: VLOOKUP - #N/A - (video inside)

    It does work. I promise you. Your enormous sheet will be back with you shortly... my PC is still loading it.
    Reply With QuoteRegister To Reply
  5. 04-26-2015, 09:59 AM #5
    domgilberto
    domgilberto is offline
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    358

    Re: VLOOKUP - #N/A - (video inside)

    Maybe it does but it's not working for me right now :P

    Video: http://screencast.com/t/DI6TQb8j4G9

    (The data is exactly the same values as what I sent you... so this is starting to become quite odd!)
    Reply With QuoteRegister To Reply
  6. 04-26-2015, 10:20 AM #6
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: VLOOKUP - #N/A - (video inside)

    Agghh.

    Secret: treat 'em all the same...

    The ones that weren't working were referring back to column C, which hadn't had ANY time subtracted from it. So... I set up another helper column and subtracted nothing from it (using TIME(0,0,0) as the function) and then rounded it just like the other columns. Big sheet will be back with you shortly by email. There are stll a few #N/As about the place - but maybe they're genuine.

    Voila.
    Reply With QuoteRegister To Reply
  7. 04-26-2015, 10:29 AM #7
    domgilberto
    domgilberto is offline
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    358

    Re: VLOOKUP - #N/A - (video inside)

    Spot on - thank you very much for your help! You've saved me from pulling out what's left on my scalp!
    Reply With QuoteRegister To Reply
  8. 04-26-2015, 10:38 AM #8
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: VLOOKUP - #N/A - (video inside)

    Thanks! It was driving me bat crazy too ... albeit not for as long as you!!
    Reply With QuoteRegister To Reply
  9. 06-11-2015, 12:06 PM #9
    domgilberto
    domgilberto is offline
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    358

    Re: VLOOKUP - #N/A - (video inside)

    I hate excel at times... it is so illogical...

    Video: http://screencast.com/t/B5JzdSdeNOLj

    Anyone got any bright ideas or am I missing something trivial here!?
    Reply With QuoteRegister To Reply
  10. 06-12-2015, 11:28 AM #10
    James McEwan
    James McEwan is offline
    Registered User
    Join Date
    01-01-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010, 2013, 2016
    Posts
    47

    Re: VLOOKUP - #N/A - (video inside)

    Hey there,

    If you use INDEX/MATCH instead of VLOOKUP this will work.

    Regards,

    J.
    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. [SOLVED] Formula to populate Line Chart & Data Automatically? - Video explaining inside!
    By domgilberto in forum Excel Charting & Pivots
    Replies: 15
    Last Post: 02-26-2013, 08:00 PM
  2. Open a video file in a specific point of the video
    By anbe45 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-10-2013, 04:03 PM
  3. Searching cells inside multiple spreadsheets inside subfolders returning vlookup 2010
    By N12NJA in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-02-2012, 04:27 PM
  4. Vlookup inside an IF - Is this possible?
    By JennyW in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-02-2006, 06:50 PM

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