Formula to contain contents of another cell

[ThunderPlane]

I need to create a MAX (with IF) formula similar to this:

=MAX(IF(C3:C12=A3,G3:G12))

But I want to keep the cell ranges dynamic and taken from another cell.

For example, cell B3 has the value "C7:C10" (this is in turn achieved by a VLOOKUP formula, and is not a text value)

Now, in the above formula I want "C3:C12" to be replaced with the contents I have in cell B3, i.e. "C7:C10", something like this:

=MAX(IF(Contents in B3=A3,G3:G100))

So finally my formula should be:

=MAX(IF(C7:C10=A3,G3:G12))

Please help me out with this. Thank you in advance for you help.

Regards,

TP.

PS: Below is a sample of the worksheet I am creating

Reference	Range	Name	Date	Time	Open	High	Low
B	C7:C10	A	03-01-2020	09:45:59	23	23	23
		A	03-01-2020	09:45:59	1535	1540	1535
		A	03-01-2020	09:45:59	0.1	0.1	0.1
		B	03-01-2020	11:45:59	0.8	0.8	0.8
		B	03-01-2020	11:45:59	1122	1122	1080.1
		B	03-01-2020	13:45:59	1019.3	1032.1	1019.3
		B	03-01-2020	09:45:59	0.9	0.9	0.9
		C	03-01-2020	10:45:59	0.9	0.9	0.9
		C	03-01-2020	11:45:59	1.1	1.1	1.1
		C	03-01-2020	13:45:59	1.3	1.3	1.25

 

[ThunderPlane]

OMG! Sorry for my noob mistake. To add the new data I did an "copy & insert cells" due to which the formula ranges changed automatically, and I forgot to update the formulae accordingly. Sincerely sorry about this ?

Your formula works perfectly, thank you very much ?

 

[Fluff]

You're welcome & thanks for the feedback.
As it happens you can just use your original formula. As A338 is the first cell that contains the relevant code, the max/if can't return a value from any row before 338.

 

[ThunderPlane]

You're welcome & thanks for the feedback.
As it happens you can just use your original formula. As A338 is the first cell that contains the relevant code, the max/if can't return a value from any row before 338.

But we are using the MAX function in Column C , where the value does get repeated.

 

[Fluff]

Yes but it's only getting the max value from col C IF col A equals AE3

 

[ThunderPlane]

Yes but it's only getting the max value from col C IF col A equals AE3

True, but what if the MAX value in col C is in a row above the col A value? I understand MAX will take the full column into consideration, and not just from the row where the earlier calculation was done. If this happens then my further calculations will incorrect, as I intend to find the MAX value after the specified date and time mentioned in cols O & P.