Array Formula in B1:
=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(A:A,SMALL(IF($A$1:$A$10<>"",ROW($A$1:$A$10)),ROWS($B$1:B1)))))
Press Ctrl+Shift+Enter and drag it down to B10.
Tip: This is also useful when you create a data validation list from a range with blank cells in it. In that case you can create a new range with non-blank cells using above formula and then use this new range for data validation.
No comments:
Post a Comment