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

[AnuLii]

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?

[PaulM100]

You can try something like:
=IFERROR(IF(VLOOKUP(A1,table,2,0)=0,"",VLOOKUP(A1,table,2,0)),"")

[Richard Buttrey]

Hi and welcome to the forum.

There looks to be some redundancy in the formula anyway.

Why not just

Please Login or Register  to view this content.

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.

[AnuLii]

this works PaulM100, thank you!

[Pete_UK]

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