Multiple index/match nested in an If statement

Hello,

I am having some issues with the following formula not returning the value I am expecting:

=IF(CONTAINS([Raw Lot ID]@row, {ActiveRawLot}), (INDEX({ActiveQCResult}, MATCH([Raw Lot ID]@row, {ActiveRawLot}))), IF(CONTAINS([Raw Lot ID]@row, {ArchivedRawlot}), (INDEX({ArchivedQCResult}, MATCH([Raw Lot ID]@row, {ArchivedRawlot}))), "Incomplete"))

What I am trying to have get done is for the formula to look for the @row value in 2 other sheets. If it exists in either, (they are mutually exclusive) then I want it to return the column value found in QC Result. If neither exists then return, "Incomplete"

Any help would be appreciated.

Find more posts tagged with

Sheets

Formulas

Accepted answers

Joseph Adams

Hello,

I was able to fix it by simply adding the MATCH type:

=IF(CONTAINS([Raw Lot ID]@row, {ActiveRawLot}), (INDEX({ActiveQCResult}, MATCH([Raw Lot ID]@row, {ActiveRawLot}, 0))), IF(CONTAINS([Raw Lot ID]@row, {ArchivedRawlot}), (INDEX({ArchivedQCResult}, MATCH([Raw Lot ID]@row, {ArchivedRawlot}, 0))), "Incomplete"))

All comments

Ramzi K

@Joseph Adams

It looks like your "Incomplete" clause is for your second condition. Try this:

=IF(CONTAINS([Raw Lot ID]@row, {ActiveRawLot}), INDEX({ActiveQCResult}, MATCH([Raw Lot ID]@row, {ActiveRawLot})), IF(CONTAINS([Raw Lot ID]@row, {ArchivedRawlot}), INDEX({ArchivedQCResult}, MATCH([Raw Lot ID]@row, {ArchivedRawlot})), "Incomplete")

I hope this helps,

Cheers,

Ramzi

Joseph Adams

Hello,

I was able to fix it by simply adding the MATCH type:

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.