# Vlookup

A

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

#### T. Valko

Try something like this:

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

A

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

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

S

#### Shane Devenshire

Hi,

You could use

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

A

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

R

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

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

#### 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.

A

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