Vlookup

[afdmello]

I have used VLOOKUP formula to get data from a list of dates for training
conducted. when I enter the staff ID in the sheet . the vlookup returns name
department and the various training dates. However, when no training is
conducted the cell returns 0-jan-00.

How can I make the cell be blank and return only the training date. if any?

Afd

 

[T. Valko]

You should've posted your formula.

Try something like this:

=IF(VLOOKUP(....)="","",VLOOKUP(....))

 

[afdmello]

This is the formula in the sheet

C2 is the staff ID number which I have to enter.
All= sheet name with data
H41008 = cell range having the staff id, name,dept, and training dates of
specific courses.

=VLOOKUP(C$2,ALL!H$4$1008,6,FALSE)

Afd

 

[T. Valko]

Ok, try it like this:

=IF(VLOOKUP(C$2,ALL!H$4$1008,6,0)="","",VLOOKUP(C$2,ALL!H$4$1008,6,0))

 

[Shane Devenshire]

Hi,

You could use

=IF(VLOOKUP(C$2,ALL!H$4$1008,6,),VLOOKUP(C$2,ALL!H$4$1008,6,),"")

 

[afdmello]

Thank you T valko.

It worked fine and the cells returned blank. However i did not change the
false attribute to 0 as you suggested.

Afd

 

[Rich/rerat]

You probably have the cell or column formatted as "d-mmm-yy", try setting
your workbook not to show zero, "0", values. Currently excel is converting
the "0" value as 0-000-00 or January 0, 2000.

In Excel, TOOLS> Options> General tab, uncheck "zero values".

You might considering changing your formula, and use an "X", or something
that is not numerical, for your zero value, if you have need to show zero
values.
=IF(VLOOKUP(C$2,ALL!H$4$1008,6,0)="","X",VLOOKUP(C$2,ALL!H$4$1008,6,0))

OR

=IF(VLOOKUP(C$2,ALL!H$4$1008,6,0)="","Need
Training",VLOOKUP(C$2,ALL!H$4$1008,6,0))


--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News) <message rule>
<<Previous Text Snipped to Save Bandwidth When Appropriate>>


Thank you T valko.

It worked fine and the cells returned blank. However i did not change the
false attribute to 0 as you suggested.

Afd

 

[T. Valko]

Ok, good deal!

The FALSE and 0 range lookup argument mean and do the exact same thing. I
prefer using 0 since it's shorter and saves a few keystrokes.

=VLOOKUP(A1,D:E,2,0)
=VLOOKUP(A1,D:E,2,FALSE)

Both of those formulas do the exact same thing.

 

[afdmello]

Yes I have formatted the cells of the whole column as a date as I will be
entering the date of the training

Afd