Formula to return a value based on the current month in the SAME sheet

Hi,

I'm having trouble getting this formula, I've tried a lot of options and am probably over-thinking it.

In the image above, what I want to return is the 5% value. I have a helper row at the top under "12/2024", 5% relates to a value for the month of December. There would be columns in this sheet through 12/2024 and the columns are NOT date columns and cannot be set as date columns. So, what formula would return "5%" if the current month is December?

Thanks in advance!!!

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

Where exactly are you planning to put that formula?

Rwoo

It would be in row 9, in a column labeled "YTD". Just a random location really.

Paul Newcome

Would the 5% always be in the same row across all of the individual month columns?

Rwoo

Yes, it will

Paul Newcome

Ok. I think I have some ideas, but can you post a screenshot of more data in the month columns so I can be sure I understand your architecture?

Rwoo

Top image is top half, bottom image is bottom half. The formula will go in the highlighted cell in the top image. The value I would want to pull for Jan would be in the highlighted image in the bottom half. I've tried helper rows 1-12 and Jan-Dec, helper date columns with 1-12 and Jan-Dec. I'm having no luck!

Appreciate the assistance Paul!

Paul Newcome

Is this going to have columns that cover multiple years, or is it just going to be 12 columns (one for each month in 2024)?

Rwoo

Just for 2024

Paul Newcome

In that case try this:

=INDEX([01/2024]27:[12/2024]27, MONTH(TODAY()))

The above will show December for now, but as soon as Jan 1 2024 hits, it will start pulling from the current month's column.

Rwoo

It returned an invalid value error. Keep in mind, the column types are Text/Number. Also, the value I want returned, in the example 5%, a formula determines that value.

Rwoo

Figured it out with this one and another post of yours, Paul:

What works is:

=INDEX([01/2024]27:[12/2024]27, 1, MATCH(MONTH(TODAY()), [01/2024]1:[12/2024]1, 0))

Where the range represents the portion of a row I want to pull the value from. Where the row_index is 1 since I want to reference 1 row. Where I'm matching today's month, with its representative # (December = "12") listed in row #1 of columns 01/2024 to 12/2024.

Thanks!!!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.