Index-match or lookup, or? extract multiple data from array

[NaverNiels]

Hi there,

I am trying to automate a meeting planner.
I have meetings down the rows, employee participants and time of meetings along the columns and market with "x" in the cells.

I would now like excel to automatically populate a new "calendar overview"-table with the times down the rows and each employee along the columns, and the have excel (based on the "x"'es in above table) to populate "free", "booked" or "conflict" under each person for each time.
See attached picture.

I have tried various forms of index match, lookup, if, countifs, etc. but nothing has worked.. I am sure someone can do the magic.

 

[excel_learnerz]

Based on the description provided, you can achieve this using a combination of COUNTIFS and IF formulas. Here's how you can create the "calendar overview" table:

1. Create a new table with times down the rows and employee names along the columns.
2. In the first cell of the new table (under the first employee and next to the first time), enter the following formula:

=IF(COUNTIFS(Sheet1!$B$2:$B$10, ">=" & $A2, Sheet1!$C$2:$C$10, "<=" & $A2, Sheet1!$D$2:$G$10, "x") = 0, "free", IF(COUNTIFS(Sheet1!$B$2:$B$10, ">=" & $A2, Sheet1!$C$2:$C$10, "<=" & $A2, Sheet1!$D$2:$G$10, "x") = 1, "booked", "conflict"))

Replace Sheet1 with the name of your sheet that contains the meeting information. Adjust the ranges $B$2:$B$10 and $C$2:$C$10 to match your meeting time ranges, and $D$2:$G$10 to match your employee columns.

1. Drag this formula across the employee columns and down the time rows in the new table.

This formula checks the number of "x" marks for each employee at each time in the original table. If there are no "x" marks, it returns "free." If there is one "x" mark, it returns "booked." If there are more than one "x" marks, it returns "conflict."

Please ensure that your meeting times are formatted consistently across both tables for the formulas to work correctly.