Iferror, index, array...

Hello,

Please see attached.

On Sheet 2014, Column K, there are blank cells and non-blank cells.

On Sheet Driver Summary, Columm B, I would like the date, Column B, of all the non-blank cells found in Column K. Any Blank cells in Column K would be ignored.

Please let me know if this is unclear as i'm not sure how to write the logic for this but i do know that it's an array, index and iferror formula involved....

Thank you in advance!

Jason

Originally Posted by Tony Valko

I didn't download your file.

See if you can adapt this to suit your needs.

Data Range

	A	B	C	D
1	Date	Status	------	Date
2	9/30/2008	Yes		9/30/2008
3	4/11/2002			4/9/2004
4	4/9/2004	No		4/7/2010
5	4/7/2010	No		1/28/2004
6	3/12/2004			4/3/2012
7	1/28/2004	Maybe		1/15/2012
8	4/3/2012	Maybe
9	1/15/2012	Yes
10	3/6/2002

This array formula** entered in D2:

=IFERROR(INDEX(A:A,SMALL(IF(B$2:B$10<>"",ROW(B$2:B$10)),ROWS(D$2:D2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as Date.

Copy down until you get blanks.

This is perfect!

Just one small glitch is that it doesn't show my the first value. I just extended the rage to include the header and works perfect.

Thanks!