With the combination of Vlookup and Indirect functions, you can dynamically change the sheet name in the formula to Vlookup multiple sheets with same formula.
How to use Vlookup multiple sheets with Indirect
In VLOOKUP Function you can search the given value from a table and give the output for matching value. In case if you have data table in same format for multiple period in different sheets, you can retrieve the data from different sheets from same formula by changing sheet reference through INDIRECT Function.
With this example, the salary details of same list of employees for different month is given in different sheets. In Summary sheet the list of employees given with the option to select the month from drop down list for which month you want to get the salary details. The name in drop down and the sheet names should be same to use INDIRECT Function. When you change the month in dropdown, VLOOKUP Function will change the sheet reference and get the data from selected month.
You can download the sample file from here.
This formula can be applied when you have large number of sheets with data in same format.