Forum Discussion
{Solved} How To: Select from a list of options to display a Label
Hello,
I am working on a spreadsheet for work for our scheduling and I ran into some difficulty. I reached a part where I am trying to set up a list for special events such as vacations or meeting so the schedule can list such instead of "OFF". I currently have it set up for one event using the formula:
=IF($A4="","",IF(AND($A4=$S$4,B$3=$R$4),$T$4,IF(Calculations!B5=0,"OFF","")))
>I want to extend the search range from just R4 and S4 to R4:R23 and S4:S23 and have it show the corresponding event that matches with it T4:T23.
>A4 contains a Employee name
>B4 contains the date of the week {i.e. 24th}
>S4 is the Name of the employee with the special event
>R4 is the Date of the special event
>T4 is the Name of the special event
>Other aspects I need to keep are that if there is no employee name listed the cell is blank and if there is no event nor time listed in B5 then it will say OFF.
Any help is appreciated. Thank-you.
- Try this:
=IF(A4=“”,””,
IFERROR(LOOKUP(2,1/(
(S$4:S$23=A4)*(R$4:R$23=B4)),
T$4:T$23),”OFF”))
4 Replies
- Try this:
=IF(A4=“”,””,
IFERROR(LOOKUP(2,1/(
(S$4:S$23=A4)*(R$4:R$23=B4)),
T$4:T$23),”OFF”))This almost got it perfect. I had to lock in a few of the cells in the formula to get them to work when copy pasted but that was easy. The only issue now is it says OFF if there is no event rather then no time in B4. I'm going to try something though and get back with an update.
I got it. Ended up with=IF($A4="","",IFERROR(LOOKUP(2,1/(($S$4:$S$23=$A4)*($R$4:$R$23=B$3)),$T$4:$T$23),IF(B4="","OFF","")))
Just had to change the ending part there.
