referenced table names inside INDEX formula

[indesignkat]

I would like to use INDEX & MATCH to return a value from another table, like this:


=INDEX(Table1[Trainer],MATCH(A2,Table1[Office],0))


So if you had an office number in A2, this would reference Table1 to find the trainer that uses that office.


The kink is that I have a big spreadsheet with lots of different tables like that, each one for a different building. So I'd like to have the formula work exactly the same way, except have it also identify which table it's referencing based on the building name.


So, office numbers in A, building names in B, and I want to put the trainer names in C. I'd like it to identify which table it needs to reference by checking the contents of column B (table names match building names), then return the trainer using index & match as shown above.


I know this sounds simple with combining of tables and whatnot, but there is a lot more data in them than just the three mentioned above, and they're pretty good-sized tables, and I don't want to mess up their structure if I don't have to.


any thoughts?


idk

 

[NARAYANK991]

Hi ,


I'll tell you what I tried out , and you can see if it applies to your problem :


1. I created two sheets labelled Building_A and Building_B.


2. In Building_B , I inserted a table Table1 , having 2 fields labelled Office and Trainer.


3. Similarly , in Building_A , I inserted a table Table2 , again having the same 2 fields viz. Office and Trainer.


4. Now , in a separate sheet , I created a lookup table as follows , in the range L2:N4 :

[pre]

Sheet Name   Table Name  Field Name
Building_B     Table1     [Office]
Building_A     Table2     [Office]

[/pre]
5. In this sheet , in cell A2 , I put in some data which is present in the tables.


6. I put in the following formula , to retrieve data from the Trainer field , looking up the data in A2 in the Office field of the tables :


=INDEX(INDIRECT("'"&L3&"'!"&M3&"[Trainer]"),MATCH(A2,INDIRECT("'"&L3&"'!"&M3&N3),0))


Narayan

 

[jeffreyweir]

indesignkat: This reminds me a lot of dynamic cascading dropdowns. You can accomplish this with just one master table. I've uploaded a file to https://docs.google.com/file/d/0B1hgC5lSuLjVWGZoaF9GM0VqbG8/edit?usp=sharing as a demo.

 

[jeffreyweir]

Sorry, that file doesn't quite work as I meant. I changed it quickly to try and meet your requirements, but stuffed something up. Will amend and repost.

 

[jeffreyweir]

Okay, here's the corrected version. It's amended from a 3-way dynamic dropdown setup I whipped up recently. Also has some code in it that clears 'downstream' choices if you later change an 'upstream' choice.


Might need further amendment for your needs, but at least this will let you see if this is something you want to look into further.


https://docs.google.com/file/d/0B1hgC5lSuLjVT1ZOellIZlJpdjQ/edit?usp=sharing