# Extract values from a range that are missing from another

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!

Thanks!

-
That works, but I can't have empty spaces i.imgur.com/ETpb1.png – Kevin Hua May 31 '12 at 3:43
check out the solution from `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

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

## Behind the scenes:

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.

-