Choose a certain cell in an array

Question

I have this spreadsheet named teams. It has four columns of soccer teams, spelled in various ways, and with varying way of abbreviations.

I got another spreadsheet were I going out use the name of the team to pick some stats.

I use Index-Match to do this today, but it is not working perfectly. Let us say I have column A to list the names the way I want them, so I can get some uniform spelling throughout my sheet. To the right of this column I have 3-5 columns with the names spelled different in each column.

How can I set up a formula that looks through columns B to G for a name like Leeds, and when it find something like it, it will pick the name in the first column, and in that row. By so I can get the cell to show Leeds, instead of Leeds United at one table, or Leeds Utd in another, and Leeds U. in yet another table.

My formula looks like this today

=IFERROR(INDEX(Teams!$B:$B;MATCH(D12;Teams!$E:$E;0));IFERROR(INDEX(Teams!$B:$B;MATCH(D12;Teams!$D:$D;0));"error"))

It seems that nesting the formula more than two times do not work. Can anyone help me out here?

If this has been solved in another question, please forgive me. I have searched this forum, and the net but failing to find a solution.

Answer 1

I don't think there is any way (without coding a macro) to get what you want.

Consider this alternative: Instead of listing all the spellings to the right of the 'good name', list them below each other, with the good name repeated in the following column. Then you can do a VLOOKUP() for the 'bad name' you have, and find in col 2 the 'good name'. You can just pour all teams in one long list, each line containing one spelling, and in the next row the 'good name' for it.

Answer 2

See example in http://bit.ly/FindAny This will list all Clubs for a given nick/name

Supposing your names are in table T starting in A1

Adding helper column Found with formula
=ISNUMBER(MATCH(x,T[[#This Row],[Club]:[Nickname4]],))*(ROW()-1)

Then for search term in I1 named x
in J2 the result formula =IFERROR(INDEX(T[Club],IF(LARGE(T[Found],ROW()-1)=0,"",LARGE(T[Found],ROW()-1))),"")
and fill down.

---

Note: if none found then the results area will be empty

Note: the formulas in the results area are "protected" against accidental change by using a Data Validation trick. I do this because actually protecting a sheet is uber annoying.

Regards

Brian