lookup and return an array

[jgedwardsv]

Hi all -

I'm trying to write a formula that will correlate two columns of data (they are equal size). I'm having trouble figuring out how to make my lookup return all the values from the column I identify (right now it only returns 1).

Here is the formula I've used:

=CORREL(TDFAnalysis[AverageEquity],INDEX(TDFAnalysis,{1,2,3,4,5,6,7,8,9,10,11,12,13,14},MATCH(TEXT([@[Fund Series]],"@"),TDFAnalysis[#Headers],0)))

{1,2,3,4,5,6,7,8,9,10,11,12,13,14} are the columns I need to return and I believe that is where the formula is breaking.

Thanks!

 

[Trouttrap2]

From the INDEX function, if you leave the ROW argument blank, it will return the rows cell values in an array, where the column MATCH has found it. You will have to Ctrl+Shft+Enter, not just enter because its an array formula. Try this.

=CORREL(TDFAnalysis[AverageEquity],INDEX(TDFAnalysis,,MATCH(TEXT([@[Fund Series]],"@"),TDFAnalysis[#Headers],0)))

 

[jgedwardsv]

Worked perfectly. Thank you. I spent 2 hours online trying to figure this out through google and youtube. Should have come here first!