Is this Concatenate/vlookup formula possible?

[WWSL14]

I'm trying to get excel to display a strong of text from a table.

In Sheet 1, Cell A1, I have a drop down menu that lists years 2012-2015.
In Sheet 1, Cell B1, I have a list of Major League Baseball teams (Arizona Diamondbacks, Atlanta Braves, etc...)
In Sheet 2, Cell Z2:Z8, I have a list of baseball teams that were the minor affiliates of a certain Major League Club for 2012. In AA2:AA8, I have the list for 2013, AB2:AB8, 2014, etc...

And the pattern in z2:ab8 repeats itself 30 times down Sheet2 for each of the 30 major league baesball clubs (Arizona, Atlanta, Baltimore, Boston, etc...)

Now, what I'm looking to accomplish is this:

As I said on Sheet 1, A1, I have a drop down menu that lists years 2012-2015.

I want to be able to pick a year, say 2014 and a team, say Boston, and have a cell like B3 display their minor league teams listed in Sheet 2, Column AB.

The part I can not figure out, and this might not be possible, is that I also want the cell in Sheet 1, B3 to display the years that the team shows up as an affiliate.

For example, in Sheet 2, under the Boston Red Sox, the first minor league affiliate would be the "Pawtucket Red Sox". The text should read the header of the column, which is the years 2012, 2013, 2014, and find that Pawtucket is listed under each column, and it should display Pawtucket (2012-2015).

Is this possible, or am I crazy?

Thanks in advance.

[Pepe Le Mokko]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Jacc]

Most likely possible. If you post a workbook with some sample data and pointers to what you want then half the job is done.

[WWSL14]

Attached is my file.

Book1.xlsx

[WWSL14]

Any chance someone has had time to look at this?

[popipipo]

What do you think of this attempt

[WWSL14]

What do you think of this attempt

That works great.

Can we try taking it one step further.

When you select say Arizona and 2012, it shows "Reno Aces." Could it show "Reno Aces (2012-2012)", so when you select Arizona and 2013, it says "Reno Aces (2012-2013)", etc... ?

[WWSL14]

That works great.

Can we try taking it one step further.

When you select say Arizona and 2012, it shows "Reno Aces." Could it show "Reno Aces (2012-2012)", so when you select Arizona and 2013, it says "Reno Aces (2012-2013)", etc... ?

Any remote chance that this is possible?

[FDibbins]

something like this?

=OFFSET(INDIRECT("Affiliates!"&ADDRESS(MATCH(Sheet2!$C$1,Affiliates!$D$1:$D$300,0),2)),ROW()-3,23+MATCH(Sheet2!$D$1,Affiliates!$Z$1:$AB$1,0),1,1)&" "&D1

[WWSL14]

something like this?

=OFFSET(INDIRECT("Affiliates!"&ADDRESS(MATCH(Sheet2!$C$1,Affiliates!$D$1:$D$300,0),2)),ROW()-3,23+MATCH(Sheet2!$D$1,Affiliates!$Z$1:$AB$1,0),1,1)&" "&D1

Kind of getting int he direction I was thinking.

Instead of saying "Reno Aces (8-7)", it would say "Reno Aces (2012-2012)", or if I selected 2013 from the dropdown, it would say "Reno Aces (2012-2013)".
But, it would have to take in account that the affiliations change, so 2012 would not always be the start year. Say, in 2014, Arizona no longer uses Reno, but instead uses Las Vegas, it would then say "Las Vegas 51's (2014-2014)", but, the second team, "Mobile Bay Bears (2012-2014)" would stay the same.