Vlookup & IF formula needed

[KClem]

Hi

I am doing some vlookups but when a cell is blank it displays a '0'. I tried adding iferror with ,"" but this did not work. The data being looked up is text and numbers.

I have attached an example. On the 'Output' sheet I need the boxes in red to be blank.

Any ideas?

Thanks

[ExcelsBiggestFan]

Try this =IF(VLOOKUP(A10,Data!A:C,3,FALSE)=0,"",VLOOKUP(A10,Data!A:C,3,FALSE))

There's probably an easier way but this works also. It's saying if your Vlookup returns zero then return blank.

Or

=SUBSTITUTE(VLOOKUP(A10,Data!A:C,3,0),0,"")

Or

=""&VLOOKUP(A3,Data!A:C,3,0)

[KClem]

Thanks Hercules1946, this is a fast fix. :-)

[Hercules1946]

This occurs when VLOOKUP doesn't find a match, which is not an error situation. I normally handle this by formatting all the cells that contain VLOOKUPs to suppress zeros. This is similar to the global suppression of zero in the Excel options, but it works better if there are parts of your workbook where you need a zero showing.
Highlight the cells that you want to suppress zeros, and create a custom format as follows:

Right Click within the selected cells and Select Format Cells/Custom.
In the Custom Window that opens, erase the Type box contents and replace with:

0;-0;;@

Click OK.

[KClem]

That works, thank you ExcelsBiggestFan :-)

[Hercules1946]

Its worth noting the custom format, because Excel does this in a number of situations, and in some cases duplicating the entire formula can be a pain, and with a large amount of data it could slow down your processing. With your particular formula, its OK.

Glad to Help

[ExcelsBiggestFan]

Welcome, but I think Hercules is the best response for this. My formula suggestions can mean slow processing if you've got a lot of data.