Does Excel have a formula I can use on my master page to look up a sheet name in another cell on the same page and then perform the usual look up, match & index etc on that 'looked-up sheet', all within the same workbook?
There is a formula called VLOOKUP. It should suit your purpose. You can access the official Microsoft website to read more about it. Since the process involves using various arguments, the best variant is to read and understand carefully in order to work with it.
Microsoft Office page: https://support.office.com/en-in/article/VLOOKUP-function-adceda66-30de-4f26-923b-7257939faa65