INDIRECT within VLOOKUP with HLOOKUP addition

[FearTheDuchess]

Hello community!

I have really been struggling with this formula for a while now, I'll try and describe it as succinctly as possible. I have an externally referenced workbook with many tabs (December 2015, January 2016, and so on...) on it containing Ticker Symbols for stocks in the first column, and a table for column periods on the horiz. axis. It looks something like this on every tab in that worksheet:

[TABLE="width: 1662"]
<colgroup><col><col><col><col span="2"><col><col><col span="3"><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Ticker[/TD]
[TD]Fund Name[/TD]
[TD]Current Month[/TD]
[TD]3 Months[/TD]
[TD]YTD[/TD]
[TD]1 Year[/TD]
[TD]2 Years[/TD]
[TD]3 Years[/TD]
[TD]4 Years[/TD]
[TD]5 Years[/TD]
[TD]6 Years[/TD]
[TD]7 Years[/TD]
[TD]8 Years[/TD]
[TD]9 Years[/TD]
[TD]10 Years[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ASFYX[/TD]
[TD]Natixis ASG Managed Futures Fund[/TD]
[TD="align: right"]-1.34%[/TD]
[TD="align: right"]6.35%[/TD]
[TD="align: right"]6.35%[/TD]
[TD="align: right"]-5.97%[/TD]
[TD="align: right"]14.60%[/TD]
[TD="align: right"]11.98%[/TD]
[TD="align: right"]7.62%[/TD]
[TD="align: right"]4.95%[/TD]
[TD]---[/TD]
[TD]---[/TD]
[TD]---[/TD]
[TD]---[/TD]
[TD]---[/TD]
[/TR]
[TR]
[TD]Newedge[/TD]
[TD]Newedge Trend Index[/TD]
[TD="align: right"]-2.91%[/TD]
[TD="align: right"]3.16%[/TD]
[TD="align: right"]3.16%[/TD]
[TD="align: right"]-4.11%[/TD]
[TD="align: right"]14.57%[/TD]
[TD="align: right"]6.95%[/TD]
[TD="align: right"]5.09%[/TD]
[TD="align: right"]3.13%[/TD]
[TD="align: right"]3.64%[/TD]
[TD="align: right"]3.21%[/TD]
[TD="align: right"]3.84%[/TD]
[TD="align: right"]5.86%[/TD]
[TD="align: right"]5.25%[/TD]
[/TR]
[TR]
[TD]AVGRX[/TD]
[TD]Dreyfus Dynamic Total Return Fund[/TD]
[TD="align: right"]2.59%[/TD]
[TD="align: right"]-2.47%[/TD]
[TD="align: right"]-2.47%[/TD]
[TD="align: right"]-7.49%[/TD]
[TD="align: right"]3.19%[/TD]
[TD="align: right"]4.88%[/TD]
[TD="align: right"]5.78%[/TD]
[TD="align: right"]5.19%[/TD]
[TD="align: right"]6.48%[/TD]
[TD="align: right"]10.50%[/TD]
[TD="align: right"]3.72%[/TD]
[TD="align: right"]2.36%[/TD]
[TD]---[/TD]
[/TR]
[TR]
[TD]MSCI Blend[/TD]
[TD]MSCI Blend[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In a separate workbook, I essentially need the formula to vlookup an indirect cell (Where I can dynamically change to March 2016, April 2016, etc), look up the ticker symbol within that sheet name, and return the correct time period column value for that month. I got the vlookup to work on one tab so far ("Current performance"), I am just having a lot of diffculty forcing the formula to read the tab names with indirect and go to the corresponding one (December 2015, March 2016, etc.). Here is what I have so far that works to return the correct period values:


VLOOKUP($G11,'Z:\EandF\BRENT\PerformanceReferences\Performance & Fund Fact Sheets\[PerformanceDatabase.xlsx]CurrentPerformance'!$A$4:$AB$250,HLOOKUP(H$10,'Z:\EandF\BRENT\PerformanceReferences\Performance & Fund Fact Sheets\[PerformanceDatabase.xlsx]CurrentPerformance'!$C$2:$O$3,2,FALSE),FALSE)


G11 is the ticker symbol
A4:AB250 is the table full of performance data (External workbook)
H10 is the column title (3 months, 1 year, 3 year)
C2:O3 are the column titles (3 months, 1 year, 3 year) (External workbook) that the H10 reference matches to.


Anyone know how to do this? I realize my description is probably incredibly confusing. Please let me know what kind of additional info I need to provide. I will take you out to dinner if you can help! Thanks!

 

[LiveToExcel]

Hi FearTheDuchess,

I think there is an underlying problem that will prevent you from completing this as desired. The root cause of your problem lies in the fact that the INDIRECT function will not work with external workbooks that are closed. The workbook being referenced in the INDIRECT function needs to be open in order for the formula to work; otherwise, a #REF error will result.

Is this the issue you are running into?

LiveToExcel

 

[FearTheDuchess]

Hey LiveToExcel,

Opening up the document hasn't be a problem, and I've tried it both ways just in case. I think the problem is that I'm getting confused how to embed the formulas within each other. I can get them to work separately but when I try to combine them there are too many " and ' and &, and I'm not sure how to implement them well. Thank you for your suggestion, I will keep it in mind when working through.

 

[Joyner]

Hello and welcome to the board.

Can you post the formulas that you say you can get to work separately? I see the first formula you say you have working, but what was the second one that works that you can't embed?

I think you are looking for something like this but hard to say based on what I understand and not seeing the whole picture (untested):

=VLOOKUP($G11,INDIRECT("'Z:\EandF\BRENT\PerformanceReferences\Performance & Fund Fact Sheets\[PerformanceDatabase.xlsx]"&"CurrentPerformance"&"'!$A$4:$AB$250"),HLOOKUP(H$10,'Z:\EandF\BRENT\PerformanceReferences\Performance & Fund Fact Sheets\[PerformanceDatabase.xlsx]CurrentPerformance'!$C$2:$O$3,2,FALSE),FALSE)

Where you would replace "CurrentPerformance" with the cell reference that would house the sheet name (again I am only guessing here), and you would apply the same logic to the HLOOKUP part.

Regarding the HLOOKUP, what are you returning that is in row 3, I assume that is a number for the column number in the lookup table? If so, I think we can simplify that with a simple MATCH function.

 

[FearTheDuchess]

Absolutely,

This is the VLOOKUP that correctly goes to "Current Performance" and returns values based on ticker symbol (rows) and timer period (columns):

=VLOOKUP($G11,'Z:\EandF\BRENT\PerformanceReferences\Performance & Fund Fact Sheets\[PerformanceDatabase.xlsx]CurrentPerformance'!$A$4:$AB$250,HLOOKUP(H$10,'Z:\EandF\BRENT\PerformanceReferences\Performance & Fund Fact Sheets\[PerformanceDatabase.xlsx]CurrentPerformance'!$C$2:$O$3,2,FALSE),FALSE)

This is the INDIRECT formula that I need to integrate into it, by forcing it to read a tab name instead of a cell name (This is a bad formula, I really need help with this one):
=VLOOKUP($G11,INDIRECT("'"&$D$1&"'!"&"$A$4:$AB$250"),4,FALSE)


With regards to the HLOOKUP, yes, that looks up the column name in the database worksheet and matches it to the destination workbook column name, can can change dynamically. The database workbook has all possible column titles for the time periods and looks like this:

[TABLE="width: 1662"]
<tbody>[TR]
[TD]Performance Master List[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ticker[/TD]
[TD]Fund Name[/TD]
[TD]Current Month[/TD]
[TD]3 Months[/TD]
[TD]YTD[/TD]
[TD]1 Year[/TD]
[TD]2 Years[/TD]
[TD]3 Years[/TD]
[TD]4 Years[/TD]
[TD]5 Years[/TD]
[TD]6 Years[/TD]
[TD]7 Years[/TD]
[TD]8 Years[/TD]
[TD]9 Years[/TD]
[TD]10 Years[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Is there any way to attach excel files to these posts? I feel like it would make this a lot easier to explain. Ha

 

[Joyner]

Hello FTD, I am just now getting this and will look at the integration part in a bit, but a couple things:

You can use a link in your post and upload the file to something like dropbox, although some users can't pick those up due to network restrictions or personal/security reasons.

For the HLOOKUP formula: HLOOKUP(H$10,'Z:\EandF\BRENT\PerformanceReferences\Performance & Fund Fact Sheets\[PerformanceDatabase.xlsx]CurrentPerformance'!$C$2:$O$3,2,FALSE)

Although no difference in the result, you should be able to replace that with something like this and you don't need the helper column number row:

MATCH(H$10,'Z:\EandF\BRENT\PerformanceReferences\Performance & Fund Fact Sheets\[PerformanceDatabase.xlsx]CurrentPerformance'!$A$2:$O$2,0) I will need to verify if there are any file open/closed issues.

 

[Joyner]

So I have a version working with the indirect based on the Ticker selected, the file, and the sheet, but to fix what you have I would need more information, a file would help but some answers would work too.:

For this:

=VLOOKUP($G11,INDIRECT("'"&$D$1&"'!"&"$A$4:$AB$250"),4,FALSE)

what do you have in Cells D1 and A4?

Why is your full data going out to column AB but you are only going out to O for the column number? Should that also be AB? Will that expand?

And of course as LiveToExcel said, the files must be open to work.

P.S. This is what I used:

VLOOKUP($G11,INDIRECT(Reference),MATCH(H$10,INDIRECT(Reference2),0),FALSE)

I used a cell with a Defined Name (Reference and Reference2) to build the path, instead of VLOOKUP I could use an INDEX and MATCH that may have a bit faster performance but it may not matter for your data.

 

[FearTheDuchess]

Hey Joyner,Is there a way I could email you the destination file and the file I am working in? I feel like it would be an easier way to walk through this. I have everything on my work comp and most file sharing sites are blocked.Let me know if this is possible. I appreciate all of your help so far!

 

[Jonmo1]

With the workbook open, the Path does not need to be included in the formula.

So your original Non-Indirect formula would be
=VLOOKUP($G11,'[PerformanceDatabase.xlsx]CurrentPerformance'!$A$4:$AB$250,HLOOKUP(H$10,'[PerformanceDatabase.xlsx]CurrentPerformance'!$C$2:$O$3,2,FALSE),FALSE)

So your indirect only needs to create the string of '[bookname]Sheetname'!Range

 

[FearTheDuchess]

I'm a bit confused by this. What would the exact formula look like if it was embedded within the Vlookup then?