There are a couple or 3 ways to do this. The obvious one (and the one I'm sure you've worked out) is to use a couple of functions, but these leave the gaps - hence the title of your thread.
BTW: the formulas I used to work out perfect attendees, but left gaps were:
In G4 I had: =IF(ISERROR(MATCH(A4,$D$4:$D$14,0)),A4,"")
...copied down to G14
In H4 I had: =IF(G4="","",VLOOKUP(G4,DATA,2,FALSE))
...copied down to H14
An alternative is to write a macro. Now I know in your initial message that you don't want to use macros, but to get rid of the gaps is going to be a pretty major undertaking to find a formula (though there are some pretty smart people on this forum so you might get lucky).
in the meantime, here is a macro which will do what you're after:
Bookmarks