Forum Discussion

Jpalaci1's avatar
Jpalaci1
Brass Contributor
Feb 16, 2021

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

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 ...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 16, 2021

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? 

  • Jpalaci1's avatar
    Jpalaci1
    Brass Contributor
    Feb 17, 2021

    SergeiBaklan 

     

    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.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Feb 17, 2021

      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))

Resources