Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 10th 19, 07:35 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2015
Posts: 8
Default Vlookup with dynamic range & a dynamic column index

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?

  #2   Report Post  
Old October 11th 19, 02:53 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2015
Posts: 3
Default Vlookup with dynamic range & a dynamic column index

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?


  #3   Report Post  
Old October 11th 19, 05:07 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2015
Posts: 8
Default Vlookup with dynamic range & a dynamic column index

I have created a copy of the file but how do I included it in this post?
  #4   Report Post  
Old October 12th 19, 05:48 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2015
Posts: 3
Default Vlookup with dynamic range & a dynamic column index

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?


  #5   Report Post  
Old October 16th 19, 04:16 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2015
Posts: 8
Default Vlookup with dynamic range & a dynamic column index

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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
INDEX useing Dynamic Range Vic Waller Excel Worksheet Functions 4 August 11th 09 03:17 AM
Dynamic Range Within INDEX BugOnPoint Excel Discussion (Misc queries) 3 July 3rd 09 10:42 PM
Dynamic Index() or Vlookup() Frank Pytel Excel Worksheet Functions 1 November 1st 08 05:35 PM
Dynamic Range Using INDEX instead of Offset [email protected] Excel Discussion (Misc queries) 4 September 19th 07 06:16 AM
dynamic, double vlookup, match, index, dget?? different workbooks Leslie Excel Worksheet Functions 11 June 27th 05 09:45 PM


All times are GMT +1. The time now is 01:38 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017