Suppose I have an Excel file with multiple sheets, and on the main sheet the user can type in a year and a name. I would like to create a formula that returns a code assigned with that name under that year. However, it is not as simple as a 2D table, as neither the names nor the codes are consistent across different years. Instead, every two columns in the second sheet are for each year, and each row is a different person. The left cell is their name and the right cell is their code.
Sheet1
Sheet2
I have tried using =INDIRECT() in combination with other formulas, but I need =INDIRECT() to return the reference to a cell, not the value within a cell.
Dylan Garcia is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.