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.

Best Answer

  • Joseph Adams
    Joseph Adams ✭✭✭✭
    Answer ✓

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

Answers

  • Ramzi K
    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

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: [email protected]

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • Joseph Adams
    Joseph Adams ✭✭✭✭
    Answer ✓

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!