How to use vlookup to reference the last sheet in Excel?

Jasperdavid - Updated on Nov 1, 2017 at 01:56 PM

I'm having an issue with an excel sheet.
It is used to calculate performance of suppliers each month. At the end of the month the calculation is saved and copied to the last worksheet (so each month # worksheets increments with 1).

there is one tab to keep in check how the suppliers were performing last 6 months (so 6 columns with the monthly score with x = current month (last worksheet) , x-1 = previous month, etc..)

Now this is with a vlookup referencing to the sheet name.
However I want the vlookup function to always look in the specified sheet , so for the column X (last worksheet) I want something like =Vlookup (Last worksheet; A1:E900); 4,0)

How can I define this "last worksheet" so that the vlookup is always checking the last worksheet in the workbook?

Likewise for the previous to last (x-1), and so on (x-2, x-3, x-4,..) with x being the last worksheet

Thanks in advance for your support!