1

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.

Raystafarian
  • 21,583
  • 11
  • 60
  • 89
Marco Stamazza
  • 111
  • 3
  • 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 02: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 02:45
  • This is very similar to [How to convert one row of data into multiple rows in Excel](http://superuser.com/q/455394/150988), [How do I split one row into multiple rows with Excel?](http://superuser.com/q/633124/150988), and [Transform multiple columns in Excel table into multiple rows](http://superuser.com/q/1022794/150988) — look at those and see whether you can adapt their answers. – Scott - Слава Україні Aug 31 '16 at 05: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)** –  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 01 '16 at 19:52
  • @Raystafarian I am working in R1C1 mode. It is kind of complicated to set up the indices. I am not sure what you mean by `indirect` (thanks for the edit :-) – Marco Stamazza Sep 02 '16 at 09:46
  • To define a cell, I think you should be able to use `indirect("R[" & row() & "]C[" & column() & "]"), then just define the other edge with adding to the row() and column() formulas – Raystafarian Sep 02 '16 at 10:18
  • @Raystafarian Yes! that's what I needed: `indirect`. many thanks. – Marco Stamazza Sep 04 '16 at 00:24

0 Answers0