Formula help

[chris9277]

Hello there,

I have a formula:

=IF($F$3="","",VLOOKUP($F$3,'DAY & NIGHT TEAM'!$A$5:$H$370,5,FALSE))

the problem I have is if the requirement is false it returns 0 what I would like it to do is return the word "OFF".

Can this be done? & if so I've got the feeling it will be something simple.

Kind regards,

Chris

 

[Aladin Akyurek]

What do you mean by "requirement"?

 

[chris9277]

Sorry if there is nothing in the cell it is looking at.

 

[T. Valko]

Hello there,

I have a formula:

=IF($F$3="","",VLOOKUP($F$3,'DAY & NIGHT TEAM'!$A$5:$H$370,5,FALSE))

the problem I have is if the requirement is false it returns 0 what I would like it to do is return the word "OFF".

Can this be done? & if so I've got the feeling it will be something simple.

Kind regards,

Chris

Do you mean the value being returned by the VLOOKUP might be 0 and you would rather it be "OFF"?

 

[Aladin Akyurek]

Sorry if there is nothing in the cell it is looking at.

Do you mean the result of the VLOOKUP bit? If so, how about custom
formatting the formula cell as:

[=0]"OFF";General

 

[chris9277]

Yes that's the one.

 

[chris9277]

Sorry, not familier with custom formatting

 

[Aladin Akyurek]

Sorry, not familier with custom formatting

Select the formula cell.
Activate Format | Cells (or select the option Format Cells after a right click).
Choose Custom.
Enter in the white box for Type:

[=0]"OFF";General

Otherwise, you'll need something like:

=IF($F$3="","",
   IF(VLOOKUP($F$3,'DAY & NIGHT TEAM'!$A$5:$H$370,5,0)="",
      "OFF",VLOOKUP($F$3,'DAY & NIGHT TEAM'!$A$5:$H$370,5,0)))

 

[T. Valko]

Yes that's the one.

Ok, try this...

=IF($F$3="","",IF(VLOOKUP($F$3,'DAY & NIGHT TEAM'!$A$5:$H$370,5,0)="","OFF",VLOOKUP($F$3,'DAY & NIGHT TEAM'!$A$5:$H$370,5,0)))

Note that if you use a custom format the TRUE CELL VALUE will still be 0 but the cell will display OFF. So, if you need to do further calculations on this data like counting how many instances of OFF there are that could cause some unnecessary problems.

 

[chris9277]

Excellent thank you very very much, that has done the job.