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

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1
2	Reference	Range	Name	Date	Time	Open	High	Low
3	B	C7:C10	A	03/01/2020	09:45:59	23	23	23
4	1122		A	03/01/2020	09:45:59	1535	1540	1535
5			A	03/01/2020	09:45:59	0.1	0.1	0.1
6			B	03/01/2020	11:45:59	0.8	0.8	0.8
7			B	03/01/2020	11:45:59	1122	1122	1080.1
8			B	03/01/2020	13:45:59	1019.3	1032.1	1019.3
9			B	03/01/2020	09:45:59	0.9	0.9	0.9
10			C	03/01/2020	10:45:59	0.9	0.9	0.9
11			C	03/01/2020	11:45:59	1.1	1.1	1.1
12			C	03/01/2020	13:45:59	1.3	1.3	1.25
Test

Cell Formulas
Range	Formula
A4	A4	=MAX(IF(INDIRECT(B3)=A3,OFFSET(INDIRECT(B3),,4)))
Press CTRL+SHIFT+ENTER to enter array formulas.

Although this will be volatile.

 

[Eric W]

Welcome to the MrExcel forum!

You could do something like this:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J
1
2	Reference	Range	Name	Date	Time	Open	High	Low		Max
3	B	C7:C10	A	3/1/2020	9:45:59	23	23	23		1122
4			A	3/1/2020	9:45:59	1535	1540	1535
5			A	3/1/2020	9:45:59	0.1	0.1	0.1
6			B	3/1/2020	11:45:59	0.8	0.8	0.8
7			B	3/1/2020	11:45:59	1122	1122	1080.1
8			B	3/1/2020	13:45:59	1019.3	1032.1	1019.3
9			B	3/1/2020	9:45:59	0.9	0.9	0.9
10			C	3/1/2020	10:45:59	0.9	0.9	0.9
11			C	3/1/2020	11:45:59	1.1	1.1	1.1
12			C	3/1/2020	13:45:59	1.3	1.3	1.25
Sheet26

Cell Formulas
Range	Formula
J3	J3	=AGGREGATE(14,6,OFFSET(INDIRECT(B3),0,4)/(INDIRECT(B3)=A3),1)

However, INDIRECT and OFFSET are volatile functions, meaning they could slow down your sheet if you have enough of them. Depending on how your range is created, there might be better ways. What does the VLOOKUP you mention look like, and the table it reads?

 

[ThunderPlane]

Thank you for your quick replies @Fluff & @Eric W .

A volatile function will not work for me as I will have 100's of these on the page, so I will request more help.

What does the VLOOKUP you mention look like, and the table it reads?

I was able to move the VLOOKUP to another column and not have the MATCH function in the reference cell. I am uploading the real workbook for your reference, it is still a work-in-progress as I am still adding new data points to it, so please bear with the additional columns and unfinished calculations.

It is a sheet to chart and backtest the historical data of a stock's price at specific times.

Column A has a lookup reference which I had to create as I wanted multiple criteria for the VLOOKUP
Columns C:K are the main table from which the data has to be extracted
Columns U:Z will have the formulas I need help with.

So cell U3 is supposed to have the highest (MAX) value from Column G but only from the row where the data in Column T was found, thats Row 338 in this case.

Same way V3 will use the MIN function

X3 should return the date on which the T3 value was 25% of T3, so the date it first hit (117.60*0.25) 29.4 or lower, if it does not go down then it can just show N/A

Same way for Y3 & Z3 to show 50% & 75% values of T3.

XL2BB will not allow over 3000 cells, so I have uploaded it on google drive. Please download the sheet from here - Scrip Data.xlsx

Again, thank you in advance for your help.

 

[Fluff]

What is the formula in T3?

 

[ThunderPlane]

What is the formula in T3?

Hi again @Fluff ,

Cell T3 is a VLOOKUP for the main data table:

=VLOOKUP(AG3,$A:$F,6,FALSE)

Were you able to download the file from Google Drive? I am kind of a newbie to this so do let me know if I should upload it some other way.

 

[Fluff]

Ok if you want the value from col G on the same row as T just use

=VLOOKUP(AG3,$A:$G,7,FALSE)

 

[ThunderPlane]

Ok if you want the value from col G on the same row as T just use

Yes I want the MAX value from Column G, but I want it to be only to be searching after the row from which the data in T was found. If you are able to download the sheet I uploaded on Google Drive, it should help you understand what I need in a better way.

Were you able to download the file from Google Drive? I am kind of a newbie to this so do let me know if I should upload it some other way.

 

[ThunderPlane]

Continuing from my earlier message:

So, cell U3 is supposed to have the highest (MAX) value from Column G but the VLOOKUP should only from the row where the data in Column T was found, thats Row 338 in this case because the cell T3 is showing the data from cell F338.

I hope this helps clarify my dilemma.

 

[Fluff]

I want it to be only to be searching after the row from which the data in T was found.

That's not what you said here

So cell U3 is supposed to have the highest (MAX) value from Column G but only from the row where the data in Column T was found,

Which do you want?