Automate Vlookup to pull in newly dumped data/update lookup range

%3CLINGO-SUB%20id%3D%22lingo-sub-2139559%22%20slang%3D%22en-US%22%3EAutomate%20Vlookup%20to%20pull%20in%20newly%20dumped%20data%2Fupdate%20lookup%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2139559%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20columns%2C%20one%20for%20new%20comments%20and%20updates%20on%20projects%20and%20a%20second%20that%20pulls%20in%20the%20old%20comments%20via%20a%20VLOOKUP%20from%20second%20archive%20tab.%20%26nbsp%3BWhen%20I%20dump%20the%20data%20into%20the%20second%20tab%20and%20move%20current%20comments%20into%20the%20archive%20so%20they%20show%20in%20the%20second%20old%20comments%20column%2C%20I'll%20change%20the%20array%20of%20the%20VLOOKUP%20based%20on%20the%20week%20they%20happened.%20I%20want%20to%20automate%20and%20not%20sure%20how.%20Should%20I%20use%20array%20formulas%3F%20Use%20the%20day%20or%20week%20formula%20to%20see%20what%20current%20week%20or%20day%20and%20then%20find%20week%20or%20day%20previous%20to%20automatically%20pull%20only%20the%20last%20week's%20comments%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2139559%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2140263%22%20slang%3D%22en-US%22%3ERe%3A%20Automate%20Vlookup%20to%20pull%20in%20newly%20dumped%20data%2Fupdate%20lookup%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2140263%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F830570%22%20target%3D%22_blank%22%3E%40Jpalaci1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20not%20sure%20why%20do%20you%20need%20to%20change%20lookup%20array%20every%20time.%20You%20may%20use%20dynamic%20ranges%2C%20or%20better%20structured%20tables%2C%20and%20use%20exactly%20the%20same%20formula%20for%20all%20dates.%20Perhaps%20you%20my%20submit%20sample%20file%2C%20just%20few%20records%20will%20be%20enough%2C%20to%20illustrate%20an%20issue%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

I have two columns, one for new comments and updates on projects and a second that pulls in the old comments via a VLOOKUP from second archive tab.  When I dump the data into the second tab and move current comments into the archive so they show in the second old comments column, I'll change the array of the VLOOKUP based on the week they happened. I want to automate and not sure how. Should I use array formulas? Use the day or week formula to see what current week or day and then find week or day previous to automatically pull only the last week's comments?

3 Replies

@Jpalaci1 

I'm not sure why do you need to change lookup array every time. You may use dynamic ranges, or better structured tables, and use exactly the same formula for all dates. Perhaps you my submit sample file, just few records will be enough, to illustrate an issue? 

@Sergei Baklan 

 

I apologize for my poor description and not providing the needed resources to answer my problem. 

 

Looking at screenshot 1, I think looking at the Date of Review and making it conditional or using a dynamic range to look at current date (always 7 days later) and pull the current week into the previous week for the current week's review. Once this current week is completed, I dump into the Archive tab and then VLOOKUP for a rolling comment archive. 

 

Thank you for any help.

@Jpalaci1 

Thank you for the sample. In general you need only Current Comments column in Archive, previous week comments could be taken with

=INDEX(Table14[Current Comments],
  MATCH(1, INDEX(
              (Table14[Account Number]=[@[Account Number]]) *
              (Table14[Date of Review]=([@[Date of Review]]-7)),0),
0))