what function to use to find data in matrix

[lovella]

Hi to ol, need help please, on what function to use in my spreadsheet.
what am working on sheet 1 is finding the max value per month from the data in sheet 2. of which i have use formula =MAX(IF(TEXT(Sheet2!A:A,"MM")="01",Sheet2!D:AY)), thank God success on this.
my prob is the value i had from this formula has a matching value on sheet3, what formula to use in order to get say value x, any chance i can upload my data here?


[TABLE="width: 193"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[lovella]

[TABLE="width: 355"]
<colgroup><col><col span="5"></colgroup><tbody>[TR]
[TD="align: right"]my sample worksheet below, i need to find out max reading per month say for jan max is 20.1924,
that value i got has a corresponding fluctuation in sheet 3, the answer i should get is [TABLE="width: 230"]
<tbody>[TR]
[TD="align: right"]0.9746, what function formula can i adopt for this please ....

[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 232"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]sheet2

1/01/2013[/TD]
[TD][/TD]
[TD="align: right"]

19.9707[/TD]
[TD][/TD]
[TD="align: right"]

19.6133[/TD]
[/TR]
[TR]
[TD="align: right"]2/01/2013[/TD]
[TD][/TD]
[TD="align: right"]20.1924[/TD]
[TD][/TD]
[TD="align: right"]20.1312[/TD]
[/TR]
[TR]
[TD="align: right"]3/01/2013[/TD]
[TD][/TD]
[TD="align: right"]15.4229[/TD]
[TD][/TD]
[TD="align: right"]15.2118[/TD]
[/TR]
[TR]
[TD="align: right"]4/01/2013[/TD]
[TD][/TD]
[TD="align: right"]10.2059[/TD]
[TD][/TD]
[TD="align: right"]11.1228[/TD]
[/TR]
[TR]
[TD="align: right"]5/01/2013[/TD]
[TD][/TD]
[TD="align: right"]11.814[/TD]
[TD][/TD]
[TD="align: right"]10.8905[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]


sheet3

[TABLE="width: 230"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD="align: right"]1-Jan-13[/TD]
[TD="align: right"]0.9754[/TD]
[TD="align: right"]0.9779[/TD]
[/TR]
[TR]
[TD="align: right"]2-Jan-13[/TD]
[TD="align: right"]0.9746[/TD]
[TD="align: right"]0.9756[/TD]
[/TR]
[TR]
[TD="align: right"]3-Jan-13[/TD]
[TD="align: right"]0.9622[/TD]
[TD="align: right"]0.9637[/TD]
[/TR]
[TR]
[TD="align: right"]4-Jan-13[/TD]
[TD="align: right"]0.9622[/TD]
[TD="align: right"]0.971[/TD]
[/TR]
[TR]
[TD="align: right"]5-Jan-13[/TD]
[TD="align: right"]0.965[/TD]
[TD="align: right"]0.9586[/TD]
[/TR]
</tbody>[/TABLE]

 

[vogel997]

[TABLE="width: 1126"]
<colgroup><col width="129" style="width: 97pt; mso-width-source: userset; mso-width-alt: 4717;"> <col width="116" style="width: 87pt; mso-width-source: userset; mso-width-alt: 4242;"> <col width="127" style="width: 95pt; mso-width-source: userset; mso-width-alt: 4644;"> <col width="64" style="width: 48pt;"> <col width="151" style="width: 113pt; mso-width-source: userset; mso-width-alt: 5522;"> <col width="914" style="width: 686pt; mso-width-source: userset; mso-width-alt: 33426;"> <tbody>[TR]
[TD="width: 129, bgcolor: white"]1/1/2013[/TD]
[TD="width: 116, bgcolor: white"]19.9707[/TD]
[TD="width: 127, bgcolor: white"]19.6133[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 151, bgcolor: transparent"][/TD]
[TD="width: 914, bgcolor: transparent"]Try this..[/TD]
[/TR]
[TR]
[TD="width: 129, bgcolor: white"]2/1/2013[/TD]
[TD="width: 116, bgcolor: white"]20.1924[/TD]
[TD="width: 127, bgcolor: white"]20.1312[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 129, bgcolor: white"]3/1/2013[/TD]
[TD="width: 116, bgcolor: white"]15.4229[/TD]
[TD="width: 127, bgcolor: white"]15.2118[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]20.1924[/TD]
[TD="bgcolor: transparent"]=LARGE(B1:C5,1)[/TD]
[/TR]
[TR]
[TD="width: 129, bgcolor: white"]4/1/2013[/TD]
[TD="width: 116, bgcolor: white"]10.2059[/TD]
[TD="width: 127, bgcolor: white"]11.1228[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 129, bgcolor: white"]5/1/2013[/TD]
[TD="width: 116, bgcolor: white"]11.814[/TD]
[TD="width: 127, bgcolor: white"]10.8905[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]0.9746[/TD]
[TD="bgcolor: transparent"]=OFFSET(B9,IFERROR(MATCH(E3,B1:B5,0),MATCH(E3,C1:C5,0))-1,IF(IFERROR(MATCH(E3,B1:B5,0),0)=0,1,0))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]sheet3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 129, bgcolor: white"]1-Jan-13[/TD]
[TD="width: 116, bgcolor: white"]0.9754[/TD]
[TD="width: 127, bgcolor: white"]0.9779[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 129, bgcolor: white"]2-Jan-13[/TD]
[TD="width: 116, bgcolor: white"]0.9746[/TD]
[TD="width: 127, bgcolor: white"]0.9756[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 129, bgcolor: white"]3-Jan-13[/TD]
[TD="width: 116, bgcolor: white"]0.9622[/TD]
[TD="width: 127, bgcolor: white"]0.9637[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 129, bgcolor: white"]4-Jan-13[/TD]
[TD="width: 116, bgcolor: white"]0.9622[/TD]
[TD="width: 127, bgcolor: white"]0.971[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 129, bgcolor: white"]5-Jan-13[/TD]
[TD="width: 116, bgcolor: white"]0.965[/TD]
[TD="width: 127, bgcolor: white"]0.9586[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[lovella]

HI Thanks vogel997,very much appreciated, thought no ones going to pick up my query. I'll try the formula. Thanks in heaps!