I looking for a Vlookup formula with dynamic range & a dynamic column index.
My spreadsheet contains our work schedule on one tab (EMHC) & a 2nd tab (Print-Out) for the print out.

The data in EMHC is by the week starting in column G22 to BJ38. In BI is our last name & in BJ is our first name (this is what we want Vlookup to find). For every week, starting in G is put our work assignments, B2 or B3, etc.

In the tab Print-Out, column B there is also B2, B3 etc.
There is a list of the dates in column I, starting with Sunday Jan/06/2019, with a drop down box to select the date desired (list from I6:I57) with its linked cell at I4.
So for the weeks of October I got this to work: =IF($I$4=40,CONCATENATE((VLOOKUP($B6,'EMHC-Schedule'!$AV$22:$BJ$38,15,FALSE))," ",(VLOOKUP($B6,'EMHC-Schedule'!$AV$22:$BJ$38,14,FALSE))))
But when November comes along, I would like to have a formula which dynamically changes: (the $I$4=?) & ($AV$22=?) & (15 or 14=?). Thoughts?