xlookup, vlookup, hlookup with and/if multiple criteria. Which do I use?

[shina67]

Hi All,

I have a problem with the below which I am hoping one of you genius people can help with.

From image 1 the cell B2 is dynamic to any day of the year. Cell F2 is also a drop down which has 16 catergories.

I need to be able to lookup in image 2 based on the 2 above criteria. As you can see in image 1 it clearly says that on 15th Feb there are 2 members of staff on holiday.
What I need is from image 2 to find out who them 2 members of Staff are.

If the date and Department changed it would need to find if anyone is on holiday from that department and on that date.

Is there a formula that would do this without the need of vba?

I am trying to build this dashboard and planner without the use of vba to try and broaden my formula knowledge.

I have come across some new formula that I haven't used before whilst building this but have come stuck on this part.

I hope what i am trying to achieve is explained properly. Any questions or help would be greatly recieved.

Thanks in advance.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.

 

[shina67]

Thanks for letting us know. Glad we could help.

How do i stop of returning #CALC! if no one on Holiday? I have tried iferror but that does not work.

 

[Alex Blakenburg]

Try add "" in the not found (if_empty) position of the Filter function, see below:

=LET(colDay,INDEX('Holiday Tracker'!$L$7:$BF$999,0,MATCH($B$2,('Holiday Tracker'!$L$4:$NM$4))),
FILTER('Holiday Tracker'!$A$7:$A$999,((colDay="H")+(colDay="HH"))*('Holiday Tracker'!$B$7:$B$999=$F$2),""))

=filter(array, include, if_empty)

 

[shina67]

 

[Alex Blakenburg]

You seem to have removed your post, does I assume that means you resolved the issue you were having.
The formula does not differentiate between dates. The most likely reason for it returning #REF is that the spreadsheet contains #REF