Using OFFSET, MATCH, ADDRESS and INDIRECT

[Megadyptes]

Hi there

I have two sheets. In the first sheet I have summary data where I have a column of dates/times, and I've used an IF statement to find the closest time from the second sheet,which is much larger. The statement is of the form:
{= MAX(IF(Sheet2!$C$:$C$13144<sheet1!a2, sheet2!$c$2:$c$13144))}
<Sheet1!L2, Sheet2!$C$2:$C$13144))}

and the smallest closest date is returned from Sheet 2 - no drama.

However, I want to copy a corresponding value that appears on the same row in Sheet 2 to Sheet 1.

At first I thought if I could find the absolute cell reference for the original value from Sheet 2, I could then nest this in the OFFSET function.

I started by determining the absolute cell reference for the original value that was returned from Sheet 2, using the formula:
=ADDRESS(MATCH(A2, Sheet1!C:C,0),3,1,1,"Sheet2!")

Which gave me the absolute cell reference, including the sheet name.

I then tried:
=OFFSET(A3, 0, 10, 1, 1), with cell A3 containing the latter formula

Unfortunately I can't seem to be able to use this returned absolute cell reference in cell A3 as part of a formula with OFFSET. If anyone has any ideas on how to do this, I would appreciate it. Ideally, I would like to know how to get Excel to recognise the returned absolute cell reference in A3 rather than A3 itself.




</sheet1!a2,>

 

[Megadyptes]

Re: Using OFFSET, MATCH, ADDRESS and INDIRECT - what am I doing wrong?

First formula is:
{= MAX(IF(Sheet2!$C$:$C$13144<sheet1!a2,><Sheet1!L2, Sheet2!$C$2:$C$13144))}

</sheet1!a2,>

 

[Megadyptes]

Re: Using OFFSET, MATCH, ADDRESS and INDIRECT - what am I doing wrong?

First formula is (getting clipped for some reason):
{= MAX(IF(Sheet2!$C$:$C$13144<sheet1!a2,> < Sheet1!L2, Sheet2!$C$2:$C$13144))}</sheet1!a2,>

 

[Aladin Akyurek]

Re: Using OFFSET, MATCH, ADDRESS and INDIRECT - what am I doing wrong?

First formula is (getting clipped for some reason):
{= MAX(IF(Sheet2!$C$:$C$13144<sheet1!a2,> < Sheet1!L2, Sheet2!$C$2:$C$13144))}</sheet1!a2,>

In which cell of Sheet1 did you implement this formula?

 

[Megadyptes]

Re: Using OFFSET, MATCH, ADDRESS and INDIRECT - what am I doing wrong?

Formula 1 is being applied in cell A2 (and works fine; the note about it being clipped has to do with posting it in this forum, not its functionality in Excel). Formula 2 is being applied in cell B2, and I've tried Formula 3 with no luck in cell C3.

I've now also tried:
=OFFSET(INDIRECT("'"&B2&"'!"&D2),0,10)

(where D2 = Sheet2, and B2= ADDRESS(MATCH(A2,Sheet2!C:C,0),3,1,1) )

but this doesn't work

 

[Aladin Akyurek]

Re: Using OFFSET, MATCH, ADDRESS and INDIRECT - what am I doing wrong?

You said: "However, I want to copy a corresponding value that appears on the same row in Sheet 2 to Sheet 1." In which range this corresponding value?

 

[Megadyptes]

Re: Using OFFSET, MATCH, ADDRESS and INDIRECT - what am I doing wrong?

The corresponding value is in Sheet 2.

I now have the OFFSET function working. Thank you for your help.

 

[Aladin Akyurek]

Re: Using OFFSET, MATCH, ADDRESS and INDIRECT - what am I doing wrong?

The corresponding value is in Sheet 2.

I now have the OFFSET function working. Thank you for your help.

You are not answering the question...