Combined Match Values with cross-sheet matching

Options
Kyle Jarrett
Kyle Jarrett ✭✭✭
edited 09/14/23 in Formulas and Functions

Hello,

I am attempting to match to two different sheets. Essentially if the linked ticket column matches the risk register ID I want to use the first formula and if it matches the BRD (second formula) I want it to use that formula. I am attempting to combine these into what I would assume would be an if statement but wanted to throw it out here and see if anyone had any easy suggestions.

Note: Both formulas below are working as expected I would just like to combine them into 1 formula.

Formula 1: If the linked ticket column matches (ex: RA-01) then I want it to show the risk.

=INDEX({PMO-### Risk Assessment Range 1}, MATCH($[Linked Ticket/Risk]12, {Risk IDs}, 0), MATCH(Risk$1, {Column Headers}, 0))


Formula 2: if the BRD# matches then I want to show the BRD (ex: BRD-01)

=INDEX({PMO-### - Business Requirements Document Range 1}, MATCH($[Linked Ticket/Risk]@row, {PMO-### - Business Requirements Document Range 2}, 0), MATCH([Requirement description]$1, {Column Headers - BRD}, 0))

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options

    @Kyle Jarrett

    Seems like IFERROR() would solve this for you:

    =IFERROR(INDEX({PMO-### Risk Assessment Range 1}, MATCH($[Linked Ticket/Risk]12, {Risk IDs}, 0), MATCH(Risk$1, {Column Headers}, 0)),INDEX({PMO-### - Business Requirements Document Range 1}, MATCH($[Linked Ticket/Risk]@row, {PMO-### - Business Requirements Document Range 2}, 0), MATCH([Requirement description]$1, {Column Headers - BRD}, 0)))

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options

    @Kyle Jarrett

    Seems like IFERROR() would solve this for you:

    =IFERROR(INDEX({PMO-### Risk Assessment Range 1}, MATCH($[Linked Ticket/Risk]12, {Risk IDs}, 0), MATCH(Risk$1, {Column Headers}, 0)),INDEX({PMO-### - Business Requirements Document Range 1}, MATCH($[Linked Ticket/Risk]@row, {PMO-### - Business Requirements Document Range 2}, 0), MATCH([Requirement description]$1, {Column Headers - BRD}, 0)))

  • Kyle Jarrett
    Options

    @Leibel S - This did, in fact work. Thank you so much. Knew it would be easier to just ask instead of messing with it.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!