Vlookup help required

[Pedros]

the lookup I am using is:
=VLOOKUP(A21,Physical!$C$5:$AN$114,27,FALSE)

I am using the lookup in a large series of consecutive rows and columns
and within the table array there are empty cells. I need the lookup to
return the value of all cells but when it arrives at a blank cell I
need it to return a blank cell. Currently when it arrives at a blank
cell it returns the date 00-Jan-00.

can anyone help?

Thanks in advance!!

 

[R..VENKATARAMAN]

try this
=if(a21="",0,VLOOKUP(A21,Physical!$C$5:$AN$114,27,FALSE))
does it help you

 

[Max]

We could use an IF error trap:

=IF(VLOOKUP(A21,Physical!$C$5:$AN$114,27,FALSE)=0,"",
VLOOKUP(A21,Physical!$C$5:$AN$114,27,FALSE))

but it comes at a cost of increased calc load & slower performance,
especially if there's lots of such formulas in the sheet/book

Another (possibly better?) option is simply to switch off the display of
zeros* in the sheet, via clicking Tools > Options > View tab > Uncheck "Zero
values" > OK

*"00-Jan-00" is simply a zero, if cell is formatted as date (dd-mmm-yy)

 

[Pedros]

Thanks Max, both options a great help!

 

[Pedros]

Problem solved.

This was the statement that solved the problem:

=IF(VLOOKUP(A9,Physical!$C$5:$AN$194,26,FALSE)="","",VLOOKUP('MS
Delivery Plan'!A9,Physical!$C$5:$AN$194,26,FALSE)

 

[Max]

You're welcome, Pedros