#### aleech13

##### New Member
Hit a road block. I am trying to create a formula that will automatically list 'A' people that are coming available in a given month. I got the formula that will list all the 'A' people, but I hit a road block on the part where it will list them only if they are coming available in the month specified. Here is my example set:

Sheet 1
 Name Type Date Available Buddy A 1/1/17 Gator B 1/19/17 Aaron C 1/15/17 Eric C 4/5/17 Ted C 5/5/17 Butch A 1/8/17

<tbody>
</tbody>

I want the names to automatically populate in the below table:

Sheet 2
 January A B C 1 (name) (name) (name) 2 (name) 3 4 5

<tbody>
</tbody>

I have only been able to write an INDEX formula to be able to list all the 'A' people, but I dont know how to make it list them conditional upon the date they are available.

=INDEX(SHEET1!A2:A7,SMALL(IF(SHEET2!B1=SHEET1!B2:B7,ROW(SHEET1!B2:B7)-MIN(ROW(SHEET1!B2:B7))+1,""),ROW(A1)))

Can anyone please help? Its almost like i need to fit in somewhere =IF(AND(SHEET1!C2:C7>=(Cell with 1/1/17),SHEET1!C2:C7<=(Cell with 1/31/17)),???????, "")

Thanks,

Aaron

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### CyrusTheVirus

##### Well-known Member
Maybe this could work? Ctrl+Shift+Enter, not just Enter.

=IFERROR(INDEX(Sheet1!\$A\$2:\$A\$7,SMALL(IF((Sheet1!\$B\$2:\$B\$7=B\$1)*(MONTH(Sheet1!\$C\$2:\$C\$7)=(MONTH(1&\$A\$1))),ROW(Sheet1!\$A\$2:\$A\$7)-ROW(Sheet1!\$A\$2)+1),ROWS(B\$2:B2))),"")

#### aleech13

##### New Member
Thank you for your response Cyrus. It appears that I am getting a #name error after hitting CTRL+SHIFT+ENTER. I looked through the formula to see if there was a text error, I didnt see one.

#### CyrusTheVirus

##### Well-known Member
Hm, it works for me based on the below setup (copy/paste the below into excel)

Enter formula into Sheet 2 Cell B2.

Sheet 1:

 Name Type Date Available Buddy A 1/1/2017 Gator B 1/19/2017 Aaron C 1/15/2017 Eric C 4/5/2017 Ted C 5/5/2017 Butch A 1/8/2017

<tbody>
</tbody>

Sheet 2:

 January A B C 1 Buddy Gator Aaron 2 Butch 3 4 5

<tbody>
</tbody>

Last edited:

#### aleech13

##### New Member
Still getting the error. It may have something to do with my excel? I was working at the office when I had the question, but came home. My Excel is 2003 at home. I think the one at the office is newer? Would that be the issue?

#### CyrusTheVirus

##### Well-known Member
Yes that could be it. I don't think Excel 2003 has the IFERROR function. So, try it again at work.

You could try this for 2003...

=IF(ISERROR(INDEX(Sheet1!\$A\$2:\$A\$7,SMALL(IF((Sheet1!\$B\$2:\$B\$7=B\$1)*(MONTH(Sheet1!\$C\$2:\$C\$7)=(MONTH(1&\$A\$1))),ROW(Sheet1!\$A\$2:\$A\$7)-ROW(Sheet1!\$A\$2)+1),ROWS(B\$2:B2)))),"",INDEX(Sheet1!\$A\$2:\$A\$7,SMALL(IF((Sheet1!\$B\$2:\$B\$7=B\$1)*(MONTH(Sheet1!\$C\$2:\$C\$7)=(MONTH(1&\$A\$1))),ROW(Sheet1!\$A\$2:\$A\$7)-ROW(Sheet1!\$A\$2)+1),ROWS(B\$2:B2))))

Last edited:

#### aleech13

##### New Member
Yup, that did it. I will try the other formula at work tomorrow.

Thank you so much Cyrus! I spent hours looking for a way to do this. I greatly appreciate your help!

You're welcome.

#### aleech13

##### New Member
Cyrus,

Is there a way to make the formula also base the return result off not just the month in which they come available, but also the year? I have some guys coming available in 2018 and its messing up my results for 2017

-Thanks!!

#### CyrusTheVirus

##### Well-known Member
could you post some examples of the issue and how you want it to look ?

Replies
1
Views
735
Replies
0
Views
3K
Replies
4
Views
907
Replies
4
Views
400
Replies
4
Views
192

1,190,918
Messages
5,983,578
Members
439,852
Latest member
balasat

### 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.

### Which adblocker are you using?

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

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