Nth occurrence of non blank is pulling in the wrong data

[mmann1973]

I cannot figure out what I'm doing wrong. I have the following data in one sheet

1-Jan
Jim
Jane 4
Mary
Jerry
John 7
Sally
Mark 7

I am trying to get the data to fill in on another excel sheet, within the same workbook, that is setup like a calendar

My expected result is:

Jane 4
John 7
Mark 7

I am using an array formula in the first cell which is pulling in correctly: (Jane 4)
=IFERROR(INDEX(Sheet1!$A$2:$A$200,MATCH(FALSE,ISBLANK(Sheet1!$B$2:$B$200),0)),"")

The second cell is pulling in the wrong data. This is the array formula I am using:
=IFERROR(INDEX(Sheet1!$A$2:$A$200,SMALL(IF(Sheet1!$B$2:$B$200="",ROW(Sheet1!$A$2:$A$200)),ROW()-1),1),"")

Any advise?

Thanks

 

[AhoyNC]

Maybe this array formula?

Excel Workbook
	A
1	Jim
2	Mary
3	Jerry
4	Sally
Sheet2

Excel Workbook
	A	B
1	1-Jan
2	Jim
3	Jane	4
4	Mary
5	Jerry
6	John	7
7	Sally
8	Mark	7
Sheet1

 

[mmann1973]

Maybe this array formula?

Thank you for your reply, but your answer is giving me all of the names with no value.
I am looking to pull all of the names with a value next to them, like this

Jane 4
John 7
Mark 7

 

[AhoyNC]

Try this array:

Excel Workbook
	A
1	Jane
2	John
3	Mark
Sheet2

Excel Workbook
	A	B
1	1-Jan
2	Jim
3	Jane	4
4	Mary
5	Jerry
6	John	7
7	Sally
8	Mark	7
Sheet1

 

[mmann1973]

AhoyNC, that worked perfect! Thank you so much for the help

 

[AhoyNC]

You're welcome. Thanks for the feedback.
If you have Excel 2010 or later you could also use the following formula which doesn't require CTRL-SHIFT-ENTER, just ENTER.

Excel Workbook
	A
1	Jane
2	John
3	Mark
sheet2

Excel Workbook
	A	B
1	1-Jan
2	Jim
3	Jane	4
4	Mary
5	Jerry
6	John	7
7	Sally
8	Mark	7
sheet1