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!!


--
Pedros
------------------------------------------------------------------------
Pedros's Profile: http://www.excelforum.com/member.php...o&userid=28202
View this thread: http://www.excelforum.com/showthread...hreadid=517645

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


"Pedros" wrote in
message ...

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!!


--
Pedros
------------------------------------------------------------------------
Pedros's Profile:
http://www.excelforum.com/member.php...o&userid=28202
View this thread: http://www.excelforum.com/showthread...hreadid=517645

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Pedros" wrote in
message ...

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!!


--
Pedros
------------------------------------------------------------------------
Pedros's Profile:
http://www.excelforum.com/member.php...o&userid=28202
View this thread: http://www.excelforum.com/showthread...hreadid=517645

Thanks Max, both options a great help!


--
Pedros
------------------------------------------------------------------------
Pedros's Profile: http://www.excelforum.com/member.php...o&userid=28202
View this thread: http://www.excelforum.com/showthread...hreadid=517645

Problem solved.

This was the statement that solved the problem:

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


--
Pedros
------------------------------------------------------------------------
Pedros's Profile: http://www.excelforum.com/member.php...o&userid=28202
View this thread: http://www.excelforum.com/showthread...hreadid=517645

You're welcome, Pedros
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Pedros" wrote in
message ...

Thanks Max, both options a great help!