Vlookup up to return blank

[Nova1979]

Hello all

I am wanting help on a vlookup formula to return a blank cell
The formula is on sheet2 column G and it is referencing a drop down options in column K

=IFNA(VLOOKUP(K1,Sheet1!$A$2:$B$53,2,FALSE),”1147”)
Sheet1 has the lists and reference numbers. Almost everything is working however, the information returned when there is a blank cell in column B from the vlookup is 0.
I am needing this to be blank however I am unable to get.it

I hope this is clear and any help is appreciated

Thanks

 

[RoryA]

If the returned value should be text, you can use:

=IFNA(VLOOKUP(K1,Sheet1!$A$2:$B$53,2,FALSE)&"",”1147”)

If it could be text or numeric, you'll need to repeat the VLOOKUP testing if the result is "" - for example:

=IFNA(IF(VLOOKUP(K1,Sheet1!$A$2:$B$53,2,FALSE)="","",VLOOKUP(K1,Sheet1!$A$2:$B$53,2,FALSE)),”1147”)

 

[Nova1979]

If the returned value should be text, you can use:

=IFNA(VLOOKUP(K1,Sheet1!$A$2:$B$53,2,FALSE)&"",”1147”)

If it could be text or numeric, you'll need to repeat the VLOOKUP testing if the result is "" - for example:

=IFNA(IF(VLOOKUP(K1,Sheet1!$A$2:$B$53,2,FALSE)="","",VLOOKUP(K1,Sheet1!$A$2:$B$53,2,FALSE)),”1147”)

Thanks very much. I have used the second as the returning information in numerical