Nth occurrence of non blank is pulling in the wrong data

mmann1973

New Member
Joined
Oct 11, 2013
Messages
46
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Maybe this array formula?

Excel Workbook
A
1Jim
2Mary
3Jerry
4Sally
Sheet2
Excel Workbook
AB
11-Jan
2Jim
3Jane4
4Mary
5Jerry
6John7
7Sally
8Mark7
Sheet1
 
Upvote 0
Try this array:
Excel Workbook
A
1Jane
2John
3Mark
Sheet2
Excel Workbook
AB
11-Jan
2Jim
3Jane4
4Mary
5Jerry
6John7
7Sally
8Mark7
Sheet1
 
Upvote 0
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
1Jane
2John
3Mark
sheet2
Excel Workbook
AB
11-Jan
2Jim
3Jane4
4Mary
5Jerry
6John7
7Sally
8Mark7
sheet1
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top