Excel: Is there a way for me to use formulas to return the value in a cell using a specific type of two-step search in another sheet?

Question

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

2023			2022			2021
Adeyemi	6433		Adeyemi	8818		Ford	7461
Combs	6453		Combs	6682		Galloway	5320
Galloway	9791		Ford	9039		Lee	7720
Johnson	9615		Lee	7099		Moreno	6014
Lee	9415		Lutz	3694		Pitts	2266
Lutz	9179		Pitts	2254		Rodriguez	2518
Moreno	2374		Rofriguez	4978		Smith	2308
Park	9290		Singh	4081
Pitts	8715		Smith	5503
Singh	8563

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.

Answer 1

With the use of CHOOSECOLS the formula:

=INDEX(A1:H11,MATCH(L2,CHOOSECOLS(A1:H11,MATCH(L1,A1:H1,0)),0),MATCH(L1,A1:H1,0)+1)

A1:H1 is the row of the years

A1:H11 is the whole range of data

Add the sheetname before the ranges like Sheet1!A1:H11 if table is on another sheet.

L1: The year to search

L2: The name to search

Without CHOOSECOLS use this formula as Rachel commented

=INDEX(A1:H11,MATCH(L2,INDEX(A1:H11,,MATCH(L1,A1:H1,0)),0),MATCH(L1,A1:H1,0)+1)

Answer 2

I noticed you said you don't have CHOOSECOLS, so I figure you might want the pre-365 OFFSET and VLOOKUP:

=VLOOKUP(L2,OFFSET(A2:H11,,MATCH(L1,A1:H1,0)-1,,2),2,FALSE)

Below use OFFSET to get the name-code table based on your selected year.

=OFFSET(A2:H11, , MATCH(L1, A1:H1, 0) - 1, , 2)

Then you just need to do a VLOOKUP on that range.