VLOOKUP sheet according to cell value

[Lux Aeterna]

Hello everyone! There's an excel workbook that contains two sheets. One named List2022 and one named Results. In the latter there is a VLOOKUP function that fetches results from the former

=IF(U2="";"";IF(AA3="";VLOOKUP(U2;List2022!$A$5:$H$10033;3;FALSE);AA3))

Now we will add a third sheet named List2023.

I was wondering if we could somehow modify the lookup function according to cell value. For example, if Y3 is 2022, I'd like the function to fetch results from the List2022 sheet. If Y3 is 2023 I'd like the function to fetch results from the List2023 sheet and so on for the years to come.

I'd like to avoid macros because there are already a few in the workbook, but if not possible I'm ok with it. I could also change sheet names if necessary.

Thank you in advance and wishes for a great new year to everyone!

 

[etaf]

=VLOOKUP(U2;INDIRECT("list"&Y3&"!$A$5:$H$10033");3;FALSE)
But is Y3 part of a date - Or text
may have to change to text
=VLOOKUP(U2;INDIRECT("list"&YEAR(Y3)&"!$A$5:$H$10033");3;FALSE)

note indirect() is a volatile function - so will update every time the spreadsheet is touched - so a lot of indirect() can slow the spreadsheet down - but a few are ok - depending on the overhead of the macros

 

[Lux Aeterna]

=VLOOKUP(U2;INDIRECT("list"&Y3&"!$A$5:$H$10033");3;FALSE)

This one seems to be working great! I am goin to use six INDIRECT() functions in each sheet. That is 12 for now, 18 in total for the next year and so on. Do you think it's going to be a problem?

 

[etaf]

Do you think it's going to be a problem?

probably not , but all depends on what else is going on , and the performance of the PC

try it now - just make 20 copies of the formula in some spare cells

 

[Lux Aeterna]

probably not , but all depends on what else is going on , and the performance of the PC

try it now - just make 20 copies of the formula in some spare cells

It doesn't seem to affect the workbook. Thanks a lot and have a great time in your holidays!

 

[etaf]

you are welcome
you too