Lookup question

  • Thread starter Thread starter dtb
  • Start date Start date
D

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?
 
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 C1:D1, 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.
 
Back
Top