Using Match function with dynamic reference (llookup array)

[rachelli]

I want to use a dynamic look up array with match and have the below example:

=MATCH(B$4,CONCATENATE("'",A5,"'!$E:$E"),0)
The above formula result is #VALUE!
Cell A5 has the value Sheet1, however, after testing this will change to a date e.g. 01-Jul-14
What am I doing wrong?
I am going to use it as part of a to build up a data table from daily sheets e.g. INDIRECT(CONCATENATE("'",A5,"'!",CONCATENATE("E",MATCH(B$4,Sheet1!$E:$E,0)))) and the indirect will be part of an OFFSET(Sheet1!$E:$E,MATCH(B$4,Sheet1!$E:$E,0)-1,-1,1,1). Or would there be a better way to go about it? INDIRECT only seems to work with a cell, rather than a column e.g. E:E, is that correct?

 

[jarjarbingie]

posting sample data and the required output would help..

 

[rachelli]

posting sample data and the required output would help..

OK, let's use a soccer theme

Sheet 1
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]For[/TD]
[TD="width: 64"]Country[/TD]
[TD="width: 64"]Against[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Australia[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Brazil[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Peru[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]USA[/TD]
[TD="align: center"]8[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]For[/TD]
[TD="width: 64"]Country[/TD]
[TD="width: 64"]Against[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]Australia[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]Brazil[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Peru[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]USA[/TD]
[TD="align: center"]3
[/TD]
[/TR]
</tbody>[/TABLE]

Data sheet - looking at these sheets but currently the Match is not dynamic: OFFSET(INDIRECT(CONCATENATE("'",A5,"'!",CONCATENATE("E",MATCH(B$4,Sheet1!$E:$E,0)))),0,-1)
[TABLE="width: 679"]
<colgroup><col><col><col span="7"></colgroup><tbody>[TR]
[TD][/TD]
[TD]For[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Against[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Australia[/TD]
[TD]Brazil[/TD]
[TD]Peru[/TD]
[TD]USA[/TD]
[TD]Australia[/TD]
[TD]Brazil[/TD]
[TD]Peru[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]Sheet1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/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]
[/TR]
[TR]
[TD]Sheet2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[jarjarbingie]

=OFFSET(INDIRECT("'"&$A5&"'!$D$1"),MATCH(B$4,INDIRECT("'"&$A5&"'!$E:$E"),0)-1,0)

where A5 = Sheet1, B4 = Australia

 

[Aladin Akyurek]

If you have sheet names like 01-Jul-14...

=MATCH(B$4,INDIRECT("'"&TEXT(A5,"dd-mm-yy")&"'!$E:$E"),0)

 

[rachelli]

This is what I ended up with - thanks so much! (my Sunday sheet is a weekly summary with the prefix 'WE ').

=IF($D367="Sunday",OFFSET(INDIRECT("'WE "&TEXT($A367,"d-mmm-yy")&"'!$a$1"),MATCH(G$1,INDIRECT("'"&TEXT($A367,"d-mmm-yy")&"'!$E:$E"),0)-1,1),OFFSET(INDIRECT("'"&TEXT($A367,"d-mmm-yy")&"'!$a$1"),MATCH(G$1,INDIRECT("'"&TEXT($A367,"d-mmm-yy")&"'!$E:$E"),0)-1,1))

It is a shame that Match doesn't seem to allow you to select more than one column as I need to update $E:$E with different columns depending on where the data is....