+ Reply to Thread
Results 1 to 5 of 5

IF(ISNA(VLOOKUP.. returns 0 instead of empty cell

Hybrid View

  1. #1
    Registered User
    Join Date
    09-03-2019
    Location
    Vantaa
    MS-Off Ver
    2013
    Posts
    2

    IF(ISNA(VLOOKUP.. returns 0 instead of empty cell

    I have this function on my sheet, cell should be empty "" or return "x" from another sheet. Problem is, it doesn't. It works if you don't touch anything, but i do have to.

    =IF(ISNA(VLOOKUP(A1,table,2,FALSE)),"",VLOOKUP(A1,table,2,FALSE))

    Sheet1 has the formula where i want the string "x" or empty cell to be, data to compare on sheet2.

    data on sheet2
    20201 x
    20049
    21677 x

    on sheet2 the data changes, and sometimes there is an "x" and sometimes no. If i remove the x from data, then the formula returns 0 instead of empty. Why?

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: IF(ISNA(VLOOKUP.. returns 0 instead of empty cell

    You can try something like:
    =IFERROR(IF(VLOOKUP(A1,table,2,0)=0,"",VLOOKUP(A1,table,2,0)),"")
    Click the * to say thanks.

  3. #3
    Registered User
    Join Date
    09-03-2019
    Location
    Vantaa
    MS-Off Ver
    2013
    Posts
    2

    Re: IF(ISNA(VLOOKUP.. returns 0 instead of empty cell

    this works PaulM100, thank you!

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

    Re: IF(ISNA(VLOOKUP.. returns 0 instead of empty cell

    A slight variation on Richard's formula:

    =IFERROR(VLOOKUP(A1,table,2,FALSE)&"","")

    which will ensure that a blank in the table will be returned as a blank and not zero.

    Hope this helps.

    Pete

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: IF(ISNA(VLOOKUP.. returns 0 instead of empty cell

    Hi and welcome to the forum.

    There looks to be some redundancy in the formula anyway.

    Why not just

    =IFERROR(VLOOKUP(A1,table,2,FALSE),"")
    But upload your workbook for a more considered response.


    To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ 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. [SOLVED] Using macro to find first empty cell in column. If range is empty returns error.
    By A440 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 05-23-2019, 11:03 AM
  2. Replies: 2
    Last Post: 08-10-2015, 10:56 PM
  3. [SOLVED] How to highlight cell ERROR MSG with if(isna) with vlookup
    By fluffyvampirekitten in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-29-2015, 10:49 PM
  4. [SOLVED] IFERROR(VLOOKUP) That returns "0" when reference cell is empty
    By Sam Capricci in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-04-2013, 01:16 AM
  5. Excel 2007 : =Average returns #Value if a cell is empty
    By mojogoon in forum Excel General
    Replies: 7
    Last Post: 11-22-2010, 02:31 PM
  6. Formula returns #Value! empty cell value
    By ingineu in forum Excel General
    Replies: 4
    Last Post: 10-02-2006, 05:49 AM
  7. isna returns a circular reference
    By Soundman in forum Excel General
    Replies: 3
    Last Post: 07-10-2006, 01:22 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