CODEWITHSUNDEEP
excel-formula
Tim
Tim

Reputation: 51

how to make Excel Show Blank instead of 0 as value

I have 2 tables with data in them ( lets call them table 1 and table 2) table number one has a unique ID column (A) and a name column(B) (which is the one i want to the data to go to)

table number 2 also has a UNique ID column (J) and a name column (K).

so the thing that i had to do was to make a function that compares the Unique ID columns and if there is a match it will take the name from Table 2 to Table 1, which i managed to do however i have a small problem which is that if there is no name give unique ID in the 2nd table it will show a 0 on table number 1 and i want it to just stay blank instead. this is the function that i wrote btw 

=IF(ISNA(MATCH(A15,$J$8:$J$40,0)),"",VLOOKUP(A15,$J$8:$K$40,2,FALSE))

Upvotes: 1

Views: 141

Answers (2)

Bigyan Bhandari
Bigyan Bhandari

Reputation: 45

This may help you; Using LEN & If Function along with Vlookup.

=IF(LEN(VLOOKUP(LookupValue,LookupRange,ColumnNumber,0))=0,"",VLOOKUP(LookupValue,LookupRange,ColumnNumber,0))

Thankyou.

Upvotes: 0

Phil A
Phil A

Reputation: 46

If a table contains any blank cells then a vlookup formula will return a 0. A solution maybe that you use nested If's so your formula might look like this:

=IF(ISNA(VLOOKUP(A15,$J$8:$J$40,0,FALSE)),"",IF(VLOOKUP(A15,$J$8:$K$40,2,FALSE)="","",VLOOKUP(A15,$J$8:$K$40,2,FALSE)))

The other solution I tried was to make sure that no cells were empty and that they at least a space in them.

Hope this helps.

Upvotes: 1

Related Questions