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]

Which do you want?

I am sorry if I caused any confusion. I will try to clarify.

The formula Cell T3 is fine, I don't need to change that. With the current VLOOKUP formula, cell T3 is showing the value of cell F338 which is in row 338.

So cell T3 will show data from Column F, and cell U3 will show data from Column G

In Cell U3 the current formula is:

{=MAX(IF($C$3:$C$1000000=$AE3,$G$3:$G$1000000))}

I want it to be starting the search from the row where the T3 data was found, that's row 338 as explained above. This is important because it does not matter what the MAX price was before we so the formula should ideally look like this:

{=MAX(IF($C$338:$C$1000000=$AE3,$G$338:$G$1000000))}

Where the row number 338 should come dynamically after the formula understands what row the cell T3 got its data from.

I hope this explains what I need.

 

[Fluff]

Ok how about

=MAX(IF(INDEX($C$3:$C$100000,MATCH(AG3,$A$3:$A$100000,0)):$C$100000=$AE3,INDEX($G$3:$G$100000,MATCH(AG3,$A$3:$A$100000,0)):$G$100000))

 

[ThunderPlane]

No, it's not working as expected, it is taking data from the upper rows too.

 

[Fluff]

Did you confirm it with Ctrl Shift Enter?

 

[ThunderPlane]

Yes I did, without the Ctrl+Shift+Enter I got the #VALUE! error.

 

[Fluff]

Did you retype the formula, or copy/paste it?

 

[ThunderPlane]

Did you retype the formula, or copy/paste it?

Yes, I did copy/paste it into the formula, and did use the Ctrl+Shift+Enter to get the { }.

I have updated the sheet to have data of the previous date (02-01-2020), it will help you understand if the formula is the previous day's data, and it also has your formula as I entered it in cell T3

You can download the updated sheet here - Scrip Data 2.xlsx

 

[Fluff]

What value do you expect the formula to return?

 

[ThunderPlane]

What value do you expect the formula to return?

U3 in the new sheet should ideally look like this:

{=MAX(IF($C$338:$C$1000000=$AE3,$G$338:$G$1000000))}

Where the row number 338 should come dynamically after the formula understands what row the cell T3 got its data from.

And the value it should return is 120, because there is no higher value than that after. With your new formula it is returning 240.

Also, I have a proper update to the sheet with the right data. Please download it here - Scrip Data 3.xlsx

 

[Fluff]

With the file from post#17 G338 is 240 so why do you think the answer should be 120?