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

For	Country	Against
1	Australia	5
2	Brazil	6
3	Peru	7
4	USA	8

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

Sheet 2

For	Country	Against
10	Australia	6
9	Brazil	5
8	Peru	4
7	USA	3

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

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)

	For				Against
	Australia	Brazil	Peru	USA	Australia	Brazil	Peru	USA
Sheet1	1	2	3	4	5	6	7	8
Sheet2	10	9	8	7	6	5	4	3

<colgroup><col><col><col span="7"></colgroup><tbody>
</tbody>

 

[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....