Lookup question

[dtb]

I want to lookup a list of van numbers
Each driver may be assigned up to 5 vans.
example:
alonzo 123456
6789
blank
fred 4567
blank
blank
joe 34567
34566
34567

My results are

123456
6789
0
4567


I want to have a list of vans without the zeros. Can you help?

 

[Max]

I'll assume that the van numbers are listed in B1 down, with intervening
blanks as you've indicated in your post

In C1: =IF(B1="","",ROW())
In D1: =IF(ROW()>COUNT(C:C),"",INDEX(B:B,SMALL(C:C,ROW())))
Select C11, copy down to cover the max expected extent of data in col B,
eg down to B200?. hide/minimize col C. Col D will dynamically return the
exact results that you seek, with all van numbers neatly bunched at the top.