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?

Explanation is difficult to understand - I don't get how are the data structured at EMHC sheet, which values you need to extract, and where to put them in Printout sheet.

If you can provide a screenshot, or the workbook itself (with dummy data, not real data, of course), it would be better.

Based on explanation, I believe INDEX/MATCH is better fit for this purpose than VLOOKUP. If you have intermediate Excel skills, google for "excel index match examples", you might find solution yourself.


On Thursday, 10 October 2019 20:35:58 UTC+2, wrote:
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?

I have created a copy of the file but how do I included it in this post?

I think best way is to upload it to Google Drive and share the link.

On Friday, 11 October 2019 18:07:12 UTC+2, wrote:
I have created a copy of the file but how do I included it in this post?

On Friday, October 11, 2019 at 10:48:29 PM UTC-6, Vuko Strugar wrote:
I think best way is to upload it to Google Drive and share the link.

On Friday, 11 October 2019 18:07:12 UTC+2, wrote:
I have created a copy of the file but how do I included it in this post?

I have uploaded the file to Google drive and given you access.