> Assume that you have the data like this

> A1 to A3
> ABC
> XYZ
> BKL

> B1 to B3
> 44

> 68

> Here B2 cell is blank.

> If you apply the below formula in C1 cell and copy and paste the c1 cell
> formula upto C3 you will notice that C2 will have the value 0.

> =VLOOKUP(A1,A:B,2,FALSE)

> But actually B2 is blank but the above formula will result zero in C2.

> Paste this formula in D1

> =IF(ISBLANK(VLOOKUP(A1,A:B,2,FALSE)),"",VLOOKUP(A1,A:B,2,FALSE))

> Copy and paste the D1 cell formula upto D3.  Now you can see that the D2
> cell will be blank.  So make use of ISBLANK function to get out of this issue.

> If this post helps, Click Yes!

> --------------------
> (Ms-Exl-Learner)
> --------------------

> "Struggling in Sheffield" wrote:

> > Hi,
> > (Excel 2003)
> > I have a workbook where sheet 1 is called 'Raw data'.
> > Sheets 2 to 13 are then called 'January' through to 'December'.
> > I use the following formulas to auto-transfer rows of data from sheet 1 'Raw
> > data' into sheets 2-13 'January' - 'December' depending on a date cell within
> > ‘Raw data’:

> > In sheet 1 ‘Raw data’:
> > =IF($B3="","",IF(TEXT($B3,"mmmm")=EK$2,ROW(),""))

> > In sheets 2-13 ‘January’ to ‘December’:
> > =IF(ROWS($2:2)>COUNT(OFFSET('Raw data'!$EJ:$EJ,,MATCH(WSN,'Raw
> > data'!$EK$2:$EV$2,0))),"",INDEX('Raw data'!E:E,SMALL(OFFSET('Raw
> > data'!$EJ:$EJ,,MATCH(WSN,'Raw data'!$EK$2:$EV$2,0)),ROWS($2:2))))

> > This all works fine except for one very perplexing problem.

> > Most of the data rows auto-transferred contain number cells, with some text
> > and a date cell. The text, date, number cells (formatted to 2 decimal places)
> > containing numbers, and some empty (blank) number cells all transfer OK (the
> > numbers are transferred as numbers (to 2 decimal places), and the blanks
> > transfer as just that - blank cells). Perfect.

> > All except for 4 cells on each row that contain whole numbers (with no
> > decimal places). Numbers within these cells transfer fine - but if these
> > cells are blank, Excel substitutes a ‘0’ (zero) value for the blank space
> > after the data transfer. This is terrible. I must have a blank space
> > transferred if the original cell value in ‘Raw data’ was a blank space. Some
> > of my calcs result in and display a zero, which is important. Excel chucking
> > in a zero on auto-transfer between sheets to replace a blank is the end of
> > the world and I must stop it.

> > For the life of me I can’t work out why some blank number cells transfer as
> > blanks, whilst Excel feels it must substitute a zero where other blank number
> > cells exist. Like I said before the only difference I can see is that the
> > number cells formatted to 2 decimal places all transfer fine, whilst those
> > formatted to 0 (zero) decimal places are giving me the problem.

> > I can’t hide the zeros as these are important for many of my calcs, nor can
> > I alter the number of decimal places for the whole number cells.

> > I’m close to spitting the dummy out with this one, Excel is just having a
> > laugh.
> > Please help.
> > Steve.

> Explicitly check for empty cells ("")

> =IF(ROWS($2:2)>COUNT(OFFSET('Raw Data'!$J:$J,,MATCH(WSN,'Raw
> Data'!$K$2:$V$2,0))),"",IF(INDEX('Raw Data'!E:E,SMALL(OFFSET('Raw
> Data'!$J:$J,,MATCH(WSN,'Raw
> Data'!$K$2:$V$2,0)),ROWS($2:2)))="","",INDEX('Raw
> Data'!E:E,SMALL(OFFSET('Raw Data'!$J:$J,,MATCH(WSN,'Raw
> Data'!$K$2:$V$2,0)),ROWS($2:2)))))

> HTH,
> Bernie
> MS Excel MVP

> "Struggling in Sheffield" <StrugglinginSheffi...@discussions.microsoft.com>
> wrote in message news:751EE9B5-E748-4E76-8A97-43D523CEE1B0@microsoft.com...
> > Hi,
> > (Excel 2003)
> > I have a workbook where sheet 1 is called 'Raw data'.
> > Sheets 2 to 13 are then called 'January' through to 'December'.
> > I use the following formulas to auto-transfer rows of data from sheet 1
> > 'Raw
> > data' into sheets 2-13 'January' - 'December' depending on a date cell
> > within
> > ‘Raw data’:

> > In sheet 1 ‘Raw data’:
> > =IF($B3="","",IF(TEXT($B3,"mmmm")=EK$2,ROW(),""))

> > In sheets 2-13 ‘January’ to ‘December’:
> > =IF(ROWS($2:2)>COUNT(OFFSET('Raw data'!$EJ:$EJ,,MATCH(WSN,'Raw
> > data'!$EK$2:$EV$2,0))),"",INDEX('Raw data'!E:E,SMALL(OFFSET('Raw
> > data'!$EJ:$EJ,,MATCH(WSN,'Raw data'!$EK$2:$EV$2,0)),ROWS($2:2))))

> > This all works fine except for one very perplexing problem.

> > Most of the data rows auto-transferred contain number cells, with some
> > text
> > and a date cell. The text, date, number cells (formatted to 2 decimal
> > places)
> > containing numbers, and some empty (blank) number cells all transfer OK
> > (the
> > numbers are transferred as numbers (to 2 decimal places), and the blanks
> > transfer as just that - blank cells). Perfect.

> > All except for 4 cells on each row that contain whole numbers (with no
> > decimal places). Numbers within these cells transfer fine - but if these
> > cells are blank, Excel substitutes a ‘0’ (zero) value for the blank space
> > after the data transfer. This is terrible. I must have a blank space
> > transferred if the original cell value in ‘Raw data’ was a blank space.
> > Some
> > of my calcs result in and display a zero, which is important. Excel
> > chucking
> > in a zero on auto-transfer between sheets to replace a blank is the end of
> > the world and I must stop it.

> > For the life of me I can’t work out why some blank number cells transfer
> > as
> > blanks, whilst Excel feels it must substitute a zero where other blank
> > number
> > cells exist. Like I said before the only difference I can see is that the
> > number cells formatted to 2 decimal places all transfer fine, whilst those
> > formatted to 0 (zero) decimal places are giving me the problem.

> > I can’t hide the zeros as these are important for many of my calcs, nor
> > can
> > I alter the number of decimal places for the whole number cells.

> > I’m close to spitting the dummy out with this one, Excel is just having a
> > laugh.
> > Please help.
> > Steve.

> .