Help with something more advanced than VLOOKUP

[mneghassi]

I would like to create a worksheet that includes pulls data for each fund, but one fund (i.e. AA Fund) at a time and for Net Purchases for fund total ($6,678.00 as of Oct 1, 2018). I would like to create a table that shows the time series.

For example, column A should be the date, 10/01/2018, 10/02/2018 and so on. Column B should be Net Purchase under fund total. I have 5 years of data for 16 funds. I tried INDEX/MATCH, but I couldn't figure it out. What formula could I put to pull the 6678 value under net purchase/fund total for AA fund as of Oct 1, and the next row in column B will pull 8678 value as of Oct 2? I am dire need of help.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]10/01/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Gross Purchases[/TD]
[TD]Exchanges-In[/TD]
[TD]Gross Redemption[/TD]
[TD]Exchanges-Out[/TD]
[TD]Net Purchases[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA Fund[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Retail[/TD]
[TD]111[/TD]
[TD]222[/TD]
[TD]155[/TD]
[TD]-233[/TD]
[TD]55[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Institutional[/TD]
[TD]519[/TD]
[TD]7050[/TD]
[TD]-333[/TD]
[TD]0[/TD]
[TD]7236[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fund Total[/TD]
[TD]842[/TD]
[TD]7272[/TD]
[TD]-1203[/TD]
[TD]-233[/TD]
[TD]6678[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BB Fund[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Retail[/TD]
[TD]2000[/TD]
[TD]3000[/TD]
[TD]-200[/TD]
[TD]-4000[/TD]
[TD]800[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Institutional[/TD]
[TD]30000[/TD]
[TD]45000[/TD]
[TD]-2000[/TD]
[TD]0[/TD]
[TD]73000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fund Total[/TD]
[TD]32000[/TD]
[TD]48000[/TD]
[TD]-31000[/TD]
[TD]-4000[/TD]
[TD]-75000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Oct 2, 2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA Fund[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Retail[/TD]
[TD]111[/TD]
[TD]222[/TD]
[TD]-155[/TD]
[TD]-233[/TD]
[TD]-55[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Institutional[/TD]
[TD]519[/TD]
[TD]7050[/TD]
[TD]-333[/TD]
[TD]0[/TD]
[TD]-503[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fund Total[/TD]
[TD]842[/TD]
[TD]7272[/TD]
[TD]-1203[/TD]
[TD]-233[/TD]
[TD]8678[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BB Fund[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Retail[/TD]
[TD]2000[/TD]
[TD]3000[/TD]
[TD]-200[/TD]
[TD]-4000[/TD]
[TD]800[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Institutional[/TD]
[TD]30000[/TD]
[TD]45000[/TD]
[TD]-2000[/TD]
[TD]0[/TD]
[TD]1500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fund Total[/TD]
[TD]34,100[/TD]
[TD]XXX[/TD]
[TD]YYY[/TD]
[TD]ZZZ[/TD]
[TD]80,935[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[SpillerBD]

Either use an OFFSET formula or transform your data into a proper data Table that can be used.
Transform data using PowerQuery

 

[mneghassi]

Either use an OFFSET formula or transform your data into a proper data Table that can be used.
Transform data using PowerQuery

I'll give it a shot

 

[Nine Zero]

Alright so this is a super workaround if your data is EXACTLY LIKE YOU MENTIONED ABOVE the only change i had to make to allow it to function properly was switch your first and second row to put the header on top

Sheet1

Sheet2 the result i believe

This is the code for this.. not sure if its what you need..... I didnt wanna continue to far in case this was not the desired result

Sub Mneghassi()
Dim q, r As Range


For Each q In Sheets("Sheet2").Range("A2:A12")
    For Each r In Sheets("Sheet1").Range("B1:B20")
        If q.Value = r.Value Then
            Sheets("Sheet2").Cells(q.Row, 2).Value = r.Offset(4, 4).Value
        End If
    Next r
Next q


End Sub

 

[mneghassi]

Alright so this is a super workaround if your data is EXACTLY LIKE YOU MENTIONED ABOVE the only change i had to make to allow it to function properly was switch your first and second row to put the header on top

Sheet1

Sheet2 the result i believe

This is the code for this.. not sure if its what you need..... I didnt wanna continue to far in case this was not the desired result

Sub Mneghassi()
Dim q, r As Range


For Each q In Sheets("Sheet2").Range("A2:A12")
    For Each r In Sheets("Sheet1").Range("B1:B20")
        If q.Value = r.Value Then
            Sheets("Sheet2").Cells(q.Row, 2).Value = r.Offset(4, 4).Value
        End If
    Next r
Next q


End Sub

That looks good. I'm getting a slighted reformatted version in an attempt to make life slightly easier

smh