Excel: sliding range lookup

Question

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

Excel: sliding range lookup

0 Answers0