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

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

[Pedros]

Thanks Max, both options a great help!

[R..VENKATARAMAN]

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


"Pedros" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Pedros]

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

[Max]

You're welcome, Pedros
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Pedros" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Max, both options a great help!