# 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
OMG! Sorry for my noob mistake. To add the new data I did an "copy & insert cells" due to which the formula ranges changed automatically, and I forgot to update the formulae accordingly. Sincerely sorry about this

Your formula works perfectly, thank you very much

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.
As it happens you can just use your original formula. As A338 is the first cell that contains the relevant code, the max/if can't return a value from any row before 338.

#### ThunderPlane

##### New Member
You're welcome & thanks for the feedback.
As it happens you can just use your original formula. As A338 is the first cell that contains the relevant code, the max/if can't return a value from any row before 338.
But we are using the MAX function in Column C , where the value does get repeated.

#### Fluff

##### MrExcel MVP, Moderator
Yes but it's only getting the max value from col C IF col A equals AE3

#### ThunderPlane

##### New Member
Yes but it's only getting the max value from col C IF col A equals AE3
True, but what if the MAX value in col C is in a row above the col A value? I understand MAX will take the full column into consideration, and not just from the row where the earlier calculation was done. If this happens then my further calculations will incorrect, as I intend to find the MAX value after the specified date and time mentioned in cols O & P.

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

1,130,142
Messages
5,640,357
Members
417,139
Latest member
bdmprasenjit

### 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