Pulling List of Data, based off of Drop Down Selection

[Afox10]

Excel Community,

Please see attached file for example.

I am wondering if there is a formula to create a list of "upcoming" machines who's date change over falls within a given drop down list year.

Using the Example:
If I have January, 2010 as my drop down selections, I would like cell D4 and the cells below (under Upcoming Change over) to spit out all of the machines (from the list tab) that their "60 Month Change Over" falls within that year.

So for the example I should get 2 Machines to show up, one in cell D4 and 1 in cell D5.

Any thoughts on this? Thank you in advance!

AFox

 

[PCosta87]

Hi,

Attached...
A combination of INDEX/SMALL/IF in array form should do it.
Copy down as necessary.

Hope this helps.

 

[Khalid NGO]

Hi AFox,
Good Day...

Check this {array formula} in Input Sheet D4 and copy down:

=IFERROR(INDEX(List!$H$2:$H$9,SMALL(IF((List!$F$2:$F$9<=--(($B$2&$B$1)+1827))*(List!$F$2:$F$9>=--(($B$2&$B$1))),ROW(List!$F$2:$F$9)-1),ROW(A1))),"")

{array formula needs to be entered with
a key combination of Ctrl+Shift+Enter}

Regards,

 

[Afox10]

All,

Sorry it took me so long to reply back.. just getting back around to the spread sheet and am able to test.

@PCosta87, your example worked! I did have to do some tweaking in the original spreadsheet- it did not like some of the table names I had created. Once I removed the table to range data, it worked. Thank you!

@Khalid NGO, still working through your formula, will report back on the success.

Thank you both for the quick replys!

 

[PCosta87]

Don't mention it
Glad I could help.