Formula to contain contents of another cell

ThunderPlane

New Member
Joined
Apr 7, 2021
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
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

ReferenceRangeNameDateTimeOpenHighLow
BC7:C10A03-01-202009:45:59232323
A03-01-202009:45:59153515401535
A03-01-202009:45:590.10.10.1
B03-01-202011:45:590.80.80.8
B03-01-202011:45:59112211221080.1
B03-01-202013:45:591019.31032.11019.3
B03-01-202009:45:590.90.90.9
C03-01-202010:45:590.90.90.9
C03-01-202011:45:591.11.11.1
C03-01-202013:45:591.31.31.25
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
+Fluff 1.xlsm
ABCDEFGH
1
2ReferenceRangeNameDateTimeOpenHighLow
3BC7:C10A03/01/202009:45:59232323
41122A03/01/202009:45:59153515401535
5A03/01/202009:45:590.10.10.1
6B03/01/202011:45:590.80.80.8
7B03/01/202011:45:59112211221080.1
8B03/01/202013:45:591019.31032.11019.3
9B03/01/202009:45:590.90.90.9
10C03/01/202010:45:590.90.90.9
11C03/01/202011:45:591.11.11.1
12C03/01/202013:45:591.31.31.25
Test
Cell Formulas
RangeFormula
A4A4=MAX(IF(INDIRECT(B3)=A3,OFFSET(INDIRECT(B3),,4)))
Press CTRL+SHIFT+ENTER to enter array formulas.


Although this will be volatile.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,823
Welcome to the MrExcel forum!

You could do something like this:

Book1 (version 1).xlsb
ABCDEFGHIJ
1
2ReferenceRangeNameDateTimeOpenHighLowMax
3BC7:C10A3/1/20209:45:592323231122
4A3/1/20209:45:59153515401535
5A3/1/20209:45:590.10.10.1
6B3/1/202011:45:590.80.80.8
7B3/1/202011:45:59112211221080.1
8B3/1/202013:45:591019.31032.11019.3
9B3/1/20209:45:590.90.90.9
10C3/1/202010:45:590.90.90.9
11C3/1/202011:45:591.11.11.1
12C3/1/202013:45:591.31.31.25
Sheet26
Cell Formulas
RangeFormula
J3J3=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

New Member
Joined
Apr 7, 2021
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What is the formula in T3?
 

ThunderPlane

New Member
Joined
Apr 7, 2021
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok if you want the value from col G on the same row as T just use
Excel Formula:
=VLOOKUP(AG3,$A:$G,7,FALSE)
 

ThunderPlane

New Member
Joined
Apr 7, 2021
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
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

New Member
Joined
Apr 7, 2021
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,898
Messages
5,638,886
Members
417,058
Latest member
BRYCEPIETROWIAK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top