IF function in an INDEX formula

Hi -

My below index formula works to capture the "2.0 Status" field; however, I am trying to incorporate an another piece to this formula that includes a job number. Not sure if its an IF or AND or additional INDEX function, but i want to pull this 2.0 status based on the "Project Kickoff Rec'd" line in the task AND for the job number in my formula sheet to match the job number in my 2.0 sheet.

=IFERROR(INDEX({2.0 Status}, MATCH("Project Kickoff Rec'd", {2.0.Task}, 0)), "N/A")

Find more posts tagged with

Accepted answers

Paul Newcome

You would use an INDEX/COLLECT instead.

=IFERROR(INDEX(COLLECT({Column To Pull From}, {First Match Column}, "first match criteria", {Second Match Column}, "second match criteria"), 1), "N/A")

All comments

Paul Newcome

You would use an INDEX/COLLECT instead.

=IFERROR(INDEX(COLLECT({Column To Pull From}, {First Match Column}, "first match criteria", {Second Match Column}, "second match criteria"), 1), "N/A")

aecross

That did the trick—thank you, Paul!

Paul Newcome

Happy to help. 👍️

aecross

@Paul Newcome — i do have another question related to using an IF statement with an INDEX function.

I only want to index a column IF it meets X criteria (i will have multiple statements that will need to index different columns, if project@row = DD&SR, index X; if project@row = CDAD, index Y, etc.

Here's what i have so far, but i'm getting #NO MATCH. Confirmed that the job # matches what's in the indexed sheet (i also have other simpler index formulas that are pulling correctly so i don't think the job # is the issue).

=IF(Project@row = "DD&SR", INDEX({Metric Sheet.DD&SR Status}, MATCH([JOB #]@row , {Metric Sheet.Job #}), 0), "N/A")

Paul Newcome

@aecross Try moving the comma and zero near the end to inside fo the MATCH statement.

You have:

{Metric Sheet.Job #}), 0), "N/A")

Try this:

{Metric Sheet.Job #}, 0)), "N/A")

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.