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 https://superuser.com/a/289653/135912 to no avail =(

Any help would be appreciated!

Thanks!

Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.

Sign up to join this community
Anybody can ask a question

Anybody can answer

The best answers are voted up and rise to the top

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 https://superuser.com/a/289653/135912 to no avail =(

Any help would be appreciated!

Thanks!

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+)

```
=IFERROR(INDEX(roster,SMALL(IF(COUNTIF(present,roster)=0,ROW()-1,""),ROW()-1),1),"")
```

Where (in my example)

`roster`

is a Named Range that refers to `$A$2:$A$21`

`present`

is a Named Range that refers to `$B$2:$B$21`

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).

Named ranges in use:

- Roster: (B3:B19)
- Present: (C3:C19)
- NotHere: (F3:F19)

Array formula entered into range (D3:D19)...

`{=IF(ISERROR(MATCH(Roster,Present,0)),Roster,"")}`

Array formulae entered into cells (E3:E19)...

`{=IFERROR(INDEX(NotHere,SMALL(IF(FREQUENCY(IF(NotHere<>"",MATCH(ROW(NotHere),ROW(NotHere)),""),MATCH(ROW(NotHere),ROW(NotHere)))>0,MATCH(ROW(NotHere),ROW(NotHere)),""),ROW(A1)),COLUMN(A1)),"")}`

`{=IFERROR(INDEX(NotHere,SMALL(IF(FREQUENCY(IF(NotHere<>"",MATCH(ROW(NotHere),ROW(NotHere)),""),MATCH(ROW(NotHere),ROW(NotHere)))>0,MATCH(ROW(NotHere),ROW(NotHere)),""),ROW(A2)),COLUMN(A2)),"")}`

etc...

Though this looks a long-winded solution, it will work no matter where the table is placed within the worksheet. It also removes `#num`

errors in Excel 2007, should you be using that version.

`jeeped`

in that link - that won't give you empty spaces - it assumes Excel 2007 or later, which version do you have? – barry houdini May 31 '12 at 13:33