IF + INT/INDEX

Hello! I'm having trouble getting this formula to work (it's giving invalid data type):

=IF(AND({OTD/FTY Range 4}) = YEAR(TODAY()); IF(AND({OTD/FTY Range 1} = "1"; IF({OTD/FTY Range 2} = "90%"; INT({OTD/FTY Range 3})))))

So what I want to do is create multiple if statements. The first is if the year column is the year today; the second is if the month column (it's not a date column) equals the number "x" ("1" being january, etc.); and the third is if the cell value equals to 90%. If all of those statements are true, then I want to index the cell value.

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Try this instead:

=IFERROR(INDEX(COLLECT({Range To Pull}, {Year Range}, @cell = YEAR(TODAY(), {Month Range}, @cell = 1, {% Range}, @cell = 0.90), 1), "")

browning.m

@Paul Newcome It´s giving incorrect argument :/

Here's a picture from the sheet I'm trying to extract this info from! The "Meta" column is actually a formula column. I tried to alter you formula to "90%" but it still isn't working

Paul Newcome

Each {Range} should only be referencing a single column. Do you have that error anywhere in any of the cells being referenced in your ranges?

browning.m

@Paul Newcome I think the problem is at the year(today()) formula... I removed it and it's working perfectly, but I still want to add that statement. Here's the formula now:

=IFERROR(INDEX(COLLECT({OTD/FTY Range 3}; {OTD/FTY Intervalo 1}; @cell = 1; {OTD/FTY Range 2}; @cell = "90%"); 1); "")

I tried to add {OTD/FTY Range 4}; @cell = YEAR(TODAY()) and it didn't work

Paul Newcome

How is the Year column being populated?

browning.m

@Paul Newcome By hand! I just digit "2023" on the cell

Paul Newcome

It looks like I missed a closing parenthesis in my first formula. Here it is corrected.:

=IFERROR(INDEX(COLLECT({Range To Pull}, {Year Range}, @cell = YEAR(TODAY()), {Month Range}, @cell = 1, {% Range}, @cell = 0.90), 1), "")

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.