Multiple index/match nested in an If statement

Joseph Adams

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.

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"))

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:

=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"))