Sorry my excel is a bit rusty.

I am trying to do a manual merging with a vlookup on a table that contains several time series. Example

      A      B     C      D
1    Date1    V1  Date2    V2
2    Jun-2010 10 Jun-2010 100
3    Jul-2010 15 Aug-2010 120

I want to create a new table with

      A       B   C
1    Date     V1  V2
2    Jun-2010 10 100
3    Jul-2010 15 NA
4    Aug-2010 NA 100 

So each column refers to the same date cell but to different ranges in the reference table. I need to slide the range by two columns every time i move one column in the output table.

So after creating a column A in the output table containing all the possible dates (months), cell B2 would have

=vlookup($A2, Tab0!A2:B3, 2, 0) 

and column C2 would have

=vlookup($A2, Tab0!C2:D3, 2, 0), 

and so on.

To do this I am trying to write a vlookup function that takes a sliding range

I tried with R1C1 references, I considered INDEX + MATCH but I always get into some kind of problems.

  • Can you clarify which columns are your "fixed" values and which would contain the magic formulas? IE: is the new table's columns B and C the columns with the magic formula? You may need to add for date Jun-2010 column B should contain xxx and column C should contain `yyyy' – Mark Stewart Aug 31 '16 at 2:30
  • Mark, i edited the question. i hope it's clearer now. i just need to slide the reference range by two cols when i move one col in the output table. thanks, – Marco Stamazza Aug 31 '16 at 2:45
  • This is very similar to How to convert one row of data into multiple rows in Excel, How do I split one row into multiple rows with Excel?, and Transform multiple columns in Excel table into multiple rows — look at those and see whether you can adapt their answers. – Scott Aug 31 '16 at 5:39
  • Use absolute reference for the array where the initial Data is located change your formulas like the following: =vlookup($A2, Tab0!$A$2:$B$3, 2, 0) and =vlookup($A2, Tab0!$C$2:$D$3, 2, 0) – user555689 Aug 31 '16 at 10:55
  • 1
    Change to r1c1 and pass row and column to indirect since it looks like it changes based on formula location – Raystafarian Sep 1 '16 at 19:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.