Vlookup with nested if's and variable range

Question

I've been trying to get a Vlookup to work more efficiently without VBA with I think is a dynamic (name) range, and without nested IF functions. I'm new here and have tried to find the solution with other posts, but couldn't work it out.

I have one workbook with item codes and a vlookup, which references to another closed workbook (so function Indirect doesn't work) with cost values. In cell D3 there is a data validation cell where the user can select which sheet in the other workbook should be referenced (e.g. File001 or File002), changing the Vlookup and returning the correct cost.

Unfortunately I can't get it to work more efficiently. I've tried Indirect, but that only works when the other workbook is open and I need it to be closed.

I'd like something simple such as:

=VLOOKUP(B3,INDIRECT("'[Book3.xlsx]" & D3 & "'!A3:B6"),2,FALSE)

which doesn't work

...and not what I use now, where it checks every time if it is File001, or 002, or 003,.. etc. and then vlookup:

=IFNA(IFS($K$2="","?",$K$2="File001",PROPER(VLOOKUP(UPPER($E13),NameRange001,4,FALSE)),$K$2="File0002",PROPER(VLOOKUP(UPPER($E13),NameRange002,4,FALSE)),$K$2="File003",PROPER(VLOOKUP(UPPER($E13),NameRange003,4,FALSE)),$K$2="File004",PROPER(VLOOKUP(UPPER($E13),NameRange004,4,FALSE)),$K$2="File005",PROPER(VLOOKUP(UPPER($E13),NameRange005,4,FALSE)),$K$2="File006",PROPER(VLOOKUP(UPPER($E13),NameRange006,4,FALSE))),"")

Code example:

I need to add more sheets from the other wb, so e.g. up to File0030, making the nested IFs extremely long. Does anyone please have an idea how to make this work more efficiently? I've been trying to improve this for maaany days.

Thanks in advance!!