Need help with formula

[Redferris]

Hello, I need a formula that can fill in a table with data from multiple sheets but with specific criteria. So for example, I have a sheet that is one specific class that has student names corresponding with grades. I have a sheet like this for each different teacher in a grade level. I want to have a master list sheet that includes all of the students from the grade level and have their grades auto populate on the master list but names and assignments matching on the master list. The problem I am having is that I want the master list in alphabetical order but the students are not in that same order in the classes so I can't just have the cells = to another cell. I would need to do that individually for each student which would take forever.  I have tried vlookup, index match, but I can't think of how to configure the formula to look up data from multiple tables on different sheets. 

Thanks

[#2]

Hi @Redferris 

for this kind of analysis I recommend the use of Power Query / Get & Transform, which you can find in the Data menue. 

With this functionality, you can load both source tables in Power Query and combine them with a join. The result can be loaded back to a worksheet.

[#3]

@Redferris 

With permission of everyone involved, if I may add this tedious workaround.

How can I merge two or more tables?

When all tables have merged into one overall table, you could filter alphabetically.

Could also work with VBA, but as you have already been advised, Power query would be the most appropriate and elegant solution.