MS2013: Adding a text array into a lookup formula

[Master_A]

Hi guys hope you can help me,

I find my array from the following formula: (dont worry about the formula but look at the output)

="$B$" & MATCH("MAB",INDIRECT("'[Test TYCTrackingsheet2014.xlsx]"&LEFT($B35,7)&"'!$A$1:$A$102"),0)&":$M$"&MATCH("TMAE",INDIRECT("'[Test TYCTrackingsheet2014.xlsx]"&LEFT($B35,7)&"'!$A$1:$A$102"),0)-1

The out put from the formula is:

$B$35:$M$57 (my array)

This array I wanna add in my vlookup formula but I get an error:

=VLOOKUP(B34,"$B$" & MATCH("MAB",INDIRECT("'[Test TYCTrackingsheet2014.xlsx]"&LEFT($B33,7)&"'!$A$1:$A$102"),0)&":$M$"&MATCH("TMAE",INDIRECT("'[Test TYCTrackingsheet2014.xlsx]"&LEFT($B33,7)&"'!$A$1:$A$102"),0)-1,1,FALSE)

I was thinking that maybe my array is a text and needs to be transfered to an array???

Can anyone help me?

[Jacc]

If you post a sample workbook it will be much easier to help you.

[Tony Valko]

Hi guys hope you can help me,

I find my array from the following formula: (dont worry about the formula but look at the output)

="$B$" & MATCH("MAB",INDIRECT("'[Test TYCTrackingsheet2014.xlsx]"&LEFT($B35,7)&"'!$A$1:$A$102"),0)&":$M$"&MATCH("TMAE",INDIRECT("'[Test TYCTrackingsheet2014.xlsx]"&LEFT($B35,7)&"'!$A$1:$A$102"),0)-1

The out put from the formula is:

$B$35:$M$57 (my array)

Assuming that's all correct, you need to put all of that inside an INDIRECT function:

INDIRECT("$B$" & MATCH("MAB",INDIRECT("'[Test TYCTrackingsheet2014.xlsx]"&LEFT($B35,7)&"'!$A$1:$A$102"),0)&":$M$"&MATCH("TMAE",INDIRECT("'[Test TYCTrackingsheet2014.xlsx]"&LEFT($B35,7)&"'!$A$1:$A$102"),0)-1)

Note that the referenced file must be open for the formula to work.