Short answer

Use the getSheets() method from the Spreadsheet object of Google Apps Script

Explanation

Google Sheets doesn't have a built-in function to get the sheet name by its position but it's possible to extended it by using Google Apps Script. The following is an example of a custom function that returns the sheet name given a valid position, otherwise return a custom error message.

function getSheetNameByPosition(position){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // var sheets = ss.getSheets();
  var result = '';
  try {
    return ss.getSheets()[position-1].getName();
  } catch(e) {
    throw new Error('The position value is not valid');
  }
}

References

Extending Google Sheets

There is no reported limit outside the number of cells. There is a limit of 50000 characters in a cell, easily tested by trying to add more, so other limits may be found which are not listed.

There are also limits inferred by taking the cell limit into account. Meaning 2 million 1 cell sheets, or 2 sheets at 1 million cells.