+ Reply to Thread
Results 1 to 14 of 14

VLOOKUP returning 0!

  1. #1
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    701

    VLOOKUP returning 0!

    Hi guys,

    I am using a VLOOKUP against data from a cell which is part of a dropdown (n/a, yes or no).

    My Vlookup is returning '0' - can anyone advise why this is?

    This is my formula: =VLOOKUP('Snapshot - Full Year'!A3, 'Q1 2018_summary'!A1:BL312, 52, FALSE)

    I am attaching an image of the cells I am trying to reference from
    Attached Images Attached Images
    Thanks,

    R.



  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    31,818

    Re: VLOOKUP returning 0!

    Can we see the sheet, rather than a non-editable picture of the sheet? Check that A3 and the target are both TEXT or both NUMBERS, but not a mixture.
    Glenn



  3. #3
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    701

    Re: VLOOKUP returning 0!

    Hi Glenn,

    I deleted a lot of data to see if I can upload the sheet and the formula works - so it is pointless uploading it as it is without the data and it just doesn't make sense. The text is all set as GENERAL.
    Any other suggestions?

  4. #4
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,096

    Re: VLOOKUP returning 0!

    Maybe you can try an Index-Match:

    =INDEX("column 52", MATCH('Snapshot - Full Year'!A3,'Q1 2018_summary'!$A$1:$A$312,0))

    "column 52" - the column that contains the results to be returned.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    31,818

    Re: VLOOKUP returning 0!

    A problem, I agree! However, without seeing what is REALLY in the source and target cells, we could be in for a lengthy guessing game.

  6. #6
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    701

    Re: VLOOKUP returning 0!

    Hi PaulM100

    Thank you for your suggestion.

    This is returning #VALUE! in all the cells unfortunately

  7. #7
    Forum Contributor
    Join Date
    07-06-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    192

    Re: VLOOKUP returning 0!

    maybe you can do a =istext or =isnumber to check on your lookup value first to confirm is it in text or number.
    Last edited by finalazy; 03-15-2018 at 05:52 AM.

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,416

    Re: VLOOKUP returning 0!

    if you know that what you are looking for in A3 is actually in 'Q1 2018_summary'!A but it is returning N/A then use =A3='Q1 2018_summary'!A3 (or wherever the values should match - in other words, find two that (should) match) and if it returns false then you know the values in the two locations are not the same. Maybe there is a space in one that isn't in the other.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  9. #9
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    701

    Re: VLOOKUP returning 0!

    how do I do this? thank you!

  10. #10
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    701

    Re: VLOOKUP returning 0!

    I am simplying looking to link the data so against A3, it needs to pull data in from the other tab (the data would be: N/A, NO or YES). I don't want to use the = method to link the data, incase the data is changed i.e the order of content in A3. Hence why I want to use a VLOOKUP

    thanks!

  11. #11
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,416

    Re: VLOOKUP returning 0!

    find a value you know is in both lists, say the first is in cell A3 in the Snapshot - Full Year tab.
    Let's say that you know this value appears in cell A27 of the Q1 2018_summary tab.
    In an empty cell put =A3=Q1 2018_summaryA27 and if it returns true they match, if it returns false then they may look like they match but they don't.

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,135

    Re: VLOOKUP returning 0!

    If you are getting 0 returned, without an IFERROR around your VLOOKUP formula, this implies that the cell to be returned is actually blank and Excel returns a zero instead. You can overcome this by doing this:

    =VLOOKUP('Snapshot - Full Year'!A3, 'Q1 2018_summary'!A1:BL312, 52, FALSE)&""

    Hope this helps.

    Pete

  13. #13
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,096

    Re: VLOOKUP returning 0!

    See attached an example.
    Attached Files Attached Files

  14. #14
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,135

    Re: VLOOKUP returning 0!

    If you delete one of those entries in column AZ of Sheet 2 (so that it is blank), you will see a zero in the corresponding place on Sheet1. You can change the formula in B1 to this:

    =INDEX(Sheet2!$AZ:$AZ,MATCH(A1,Sheet2!A:A,0))&""

    to avoid this happening.

    Hope this helps.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Using vLookup based on results from a vLookup & returning an undetermined list
    By NormalityBan in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 08-14-2014, 05:02 AM
  2. [SOLVED] Why is this VLOOKUP returning N/A?
    By brewcitybiz in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-07-2012, 07:32 PM
  3. Vlookup is returning a N/A
    By lsing005 in forum Excel General
    Replies: 5
    Last Post: 01-20-2012, 04:08 PM
  4. Vlookup returning #N/A
    By Laure in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-19-2009, 04:43 AM
  5. Vlookup returning #N/A
    By Clash in forum Excel General
    Replies: 5
    Last Post: 10-03-2007, 06:14 PM
  6. [SOLVED] vlookup returning sum
    By Rose in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  7. [SOLVED] Vlookup returning #N/A
    By ww in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-22-2005, 08:08 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1