Variable Index Match

[mozga628]

hello, the company i work for maintains an employee list in an SQL database that i have imported into excel as a table. i have i used the rank function so that each employee can be referenced alphabetically by team.


i have also created a drop down that lists the team name, and another drop down that lists data columns in the table.

my starting point is this:
=INDEX(Agents[FullName],MATCH(row()-1,Agents[TEAM1],0))
this will give me the full name of the first person (alphabetically) on TEAM1 assuming we are on row 2.

how can i change "FullName" and "TEAM1" to be user defined from the drop down list? (preferably without endless nested logic)
=IFERROR(INDEX(Agents[$A$1],MATCH(row()-1,Agents[$A$2],0)),"")

my goal is to be able to select a team and any employee data on the table: Example: John Dough Domain\UID Telephone# ETC

 

[mozga628]

=index(indirect("Agents["&$G$1&"]"),match(row()-1,indirect("Agents["&$B$1&"]"),0))
essentially the indirect command allowed me to build the search manually!
i solved my own question, but i hope this helps someone else, cuz it gave me a heck of a challenge!