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
 

ThunderPlane

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

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
Ok how about
Excel Formula:
=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))
 
Solution

ThunderPlane

New Member
Joined
Apr 7, 2021
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
No, it's not working as expected, it is taking data from the upper rows too.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
Did you confirm it with Ctrl Shift Enter?
 

ThunderPlane

New Member
Joined
Apr 7, 2021
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
Did you retype the formula, or copy/paste it?
 

ThunderPlane

New Member
Joined
Apr 7, 2021
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
What value do you expect the formula to return?
 

ThunderPlane

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
With the file from post#17 G338 is 240 so why do you think the answer should be 120?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,129
Messages
5,640,296
Members
417,135
Latest member
zeusmining

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