# Formula to contain contents of another cell

#### ThunderPlane

##### New Member
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))

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

##### New Member
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
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))``

#### ThunderPlane

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

#### Fluff

##### MrExcel MVP, Moderator
Did you confirm it with Ctrl Shift Enter?

#### ThunderPlane

##### New Member

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

#### Fluff

##### MrExcel MVP, Moderator
Did you retype the formula, or copy/paste it?

#### ThunderPlane

##### New Member

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

#### Fluff

##### MrExcel MVP, Moderator
What value do you expect the formula to return?

#### ThunderPlane

##### New Member
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
With the file from post#17 G338 is 240 so why do you think the answer should be 120?

Replies
4
Views
64
Replies
3
Views
76
Replies
11
Views
291
Replies
9
Views
48
Replies
6
Views
133

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.

### Which adblocker are you using?

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

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