I've got two columns in Excel, "ROSTER" and "PRESENT", shown below:
Is there a formula to achieve the "NOT HERE" column? I tried using
VLOOKUP() and http://superuser.com/a/289653/135912 to no avail =(
Any help would be appreciated!
There is no built-in function that can singlehandedly do this task.
You can try this array formula in the "Not Here" column (MS Excel 2007+)
Where (in my example)
To enter the formula, select the cells in the Not Here column (in my case it's C2 down to C21), type the formula and then press Ctrl+Shift+Enter
This may be a bit overkill, but it works. Hopefully you don't mind having an intermediate 'Not Here' column with spaces, before arriving at the end result (Not Here 2).
Behind the scenes:
Named ranges in use:
Array formula entered into range (D3:D19)...
Array formulae entered into cells (E3:E19)...
Though this looks a long-winded solution, it will work no matter where the table is placed within the worksheet. It also removes