to have the value of a certain cell returned

[Sandor_Beckers_1969]

Dear all,

I have a large spreadsheet. A simplified version of the spreadsheet looks like this:

I am investigating the decrease of strength and volume linked to a certain date. What I did first in cell M3 was to have the oldest date in that row returned by using the formula =MIN(A3;D3;G3;J3). The result is cell A3.

That was the easy part. Next, I want to have the volume of the liquid at that date returned in cell N3. In this particular case, that means I would need to have the value of cell C3 returned. 

Unfortunately, I have not been able to find the correct formula which would give me the result I need. I've tried OFFSET but strangely that does not seem work. 

Any feedback which would help me solve this challenging issue will be highly appreciated!

Many thanks in advance,

Sándor

[#2]

@Sandor_Beckers_1969 ,,,

If this is only the problem then in N3,, enter this =C3, you get the value of C3 in cell N3.

Please be specific ,,, if you do have any other issue,, since I guess that is a different one,, !

Or use this formula in cell N3:

=INDEX(A3:L3,MATCH(M3,A3:L3,0),3)

Here 3 is column number i.e. C3 for volume.

[#3]

Hi Rajesh-S,

Thank you for your reply.

I think it is relevant to be aware that for row 3, the value I need to have returned is C3, but in row 4 (please see below figure) the value to be returned would be F4. This is because of the use of the MIN formula in column M. 

In other words, I am afraid the required formula in column N is definitely a more complex one than the direct reference to the cell containing the value. 

I hope this may trigger you in the right direction; thanks in advance for your help!

[#4]

I did not see the additional text you posted, sorry, I am checking the formula you added now.

[#5]

Hi again Rajesh-S,

If I copy the formula you suggested to cell N4, I am getting a #REF! error message. Could that be caused by the fact that the first three cells in row 4 are effectively empty?

[#6]

@Sandor_Beckers_1969 ,,  wait for a while.

[#7]

@Sandor_Beckers_1969 ,,

Use this formula in N3:

=IF(YEAR($M3)=$A$1,INDEX($A3:$C3,MATCH($M3,$A3:$C3,0),3),IF(YEAR($M3)=$D$1,INDEX($D3:$F3,MATCH($M3,$D3:$F3,0),3),IF(YEAR($M3)=$G$1,INDEX($G3:$I3,MATCH($M3,$G3:$I3,0),3),IF(YEAR($M3)=$J$1,INDEX($J3:$L3,MATCH($M3,$J3:$L3,0),3))))

Note:

Cell A1, D1, G1 & J1 has year (merged cells), and 3 is column number of every Range.

Adjust cell references in the formula as needed.

[#8]

Hi Rajesh-S,

You're my hero of the day. It does the trick!

Many, many thanks for sharing your knowledge!

Best regards,

Sándor

[#9]

Glad to help you Sandor_Beckers_1969 ,,, keep asking ☺

[#10]

Hi @Sandor_Beckers_1969 ,,, below is the shortest / smart method.

• Formula in cell H62: 

=MIN(A62,D62)

• Enter this in cell I62:

=IFERROR(INDEX($A62:$F62,MATCH($H62,$A62:$F62,0)+2),"")

Note: 

• Where +2 is OFFSET position, formula finds earliest date in cell H62, in range A62:F62, and gets the value from 2 next column, i.e. C in case of 18/09/2015, and for others.
• Adjust cell references in the formula as needed (according to your data range).

[#11]

@Sandor_Beckers_1969 

I'd adjust bit more since data could be not for every year and we shall exclude it, thus

=AGGREGATE(15,6,1/(INDEX(A6:K6,1,{1,4,7,10})>0)*INDEX(A6:K6,1,{1,4,7,10}),1)

and
=INDEX(A6:L6,MATCH(M6,A6:L6,0)+2)

[#12]

Dear Sergei Baklan and Rajesh-S,

Thank you both for another reply and another piece of equipment I can use to work on my sheet. I will probably be back with a request for additional feedback but until then; thanks again!

Best regards,

Sándor

[#13]

@Sandor_Beckers_1969 , you are welcome. Please play with variants and ask questions if something is unclear.

[#14]

@Sandor_Beckers_1969 ,,,

This is the single shot solution,, no need of HELPER data (earliest DATE in H62:H65).

• Formula in I62:

=IFERROR(INDEX($A62:$F62,MATCH(MIN(A62,D62),$A62:$F62,0)+2),"")

• Adjust cell references in the formula as needed.

[#15]

Good day gentlemen,

I have made good progress but as expected, something new has turned up which make me asking for help again. 

In short, the question is this: I have added a "special event" to the spreadsheet. In case a date is present in this section, it is a so called "new starting point". Now if I want to look up the next date after this event, I wonder what formula I should use in cell R3? The result of this formula should return the value of cell J3 (marked green). In row 4, the returned value of the formula should be cell J4 (also marked green):

Last but not least, I already entered the formula for the volume that should be returned in cells S3 and S4 thanks to your previous input ;-).

Many thanks in advance for your help!

Best regards,

Sándor

[#16]

@Sandor_Beckers_1969 

As variant

=AGGREGATE(15,6,1/(INDEX(A6:K6,1,{1,4,7,10})>M6)*INDEX(A6:K6,1,{1,4,7,10}),1)

[#17]

@Sandor_Beckers_1969 ,,

Your need is find the closest date is greater than the specific date,, and for this you need an array ( CSE ) formula.

N.B.  For better visualization and understanding I've used only Dates.

Use this in cell N16, finish with Ctrl+Shift+Enter & fill down.

{=MAX(($H16:$K16>=$M16)*$H16:$K16)}

Adjust cell references in the formula as needed.

[#18]

Hi Sergei Baklan,

I tried your formula but I get a #NUM! error. As being relatively new to Excel, the formula looks quite complicted to me. What does the use of brackets / parantheses {} necessary to make it work? And the slash (/)?

Many thanks in advance for your reply.

[#19]

Hi Rajesh-S,

After using the formula you suggested, I receive a #VALUE! error. What I also noticed is that in your example, the returned value in row 17 seems to be wrong because it should be I17 instead of K17?

[#20]

You are getting #VALUE error coz you have not finished the formula with Ctrl+Shift+Enter. Read instructions while using the formula,, the screen shot I've posted here is part of my worksheet,,, and I've posted after is been tested properly. And always remember you need to adjust cell references as needed. ☺