Index Match function with specific criteria

Hello,

I have successfully put together a formula to pull an Investigation tracking number to a cell when the product name on the new sheet matches the product name on the Investigation Tracker sheet.

=IFERROR(INDEX({Investigation Tracker Range 1}, MATCH([PO Item Description]@row, {Investigation Tracker Range 3}, 0)), "")

What I would now like to do is to only have this pull the investigation number when the Investigation Tracker status column is anything but "closed".

Any advise is appreciated.

Tom

Find more posts tagged with

Formulas

Accepted answers

All comments

DKazatsky2

Hi @tmckivergan,

Give this a try.

=IFERROR(INDEX(COLLECT({Investigation Tracker Range 1}, {Investigation Tracker Range 3}, =[PO Item Description]@row, {Investigation Tracker Range 4}, <>"closed"), 1), "")

{Investigation Tracker Range 4} is a cross-sheet reference to the status column.

Hope this helps,

Dave

tmckivergan

That worked perfectly. Thank you very much

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.