# Help with something more advanced than VLOOKUP

#### mneghassi

##### New Member
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.

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

<tbody>
</tbody>

#### SpillerBD

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

#### mneghassi

##### New Member
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

##### Well-known Member
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

Code:
``````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``````

Last edited:

#### mneghassi

##### New Member
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

Code:
``````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

Last edited: