Referencing 2 sheets with different outcomes

Options

Trying to refence 2 sheets and after each sheet reference bring back either Approved, Not Approved, and if not on either bring back Not Found.

I'm using an IFERROR function but can figure out how to bring back the verbiage I want. Here is the fx I'm using:

=IFERROR(MATCH([Requestor ]@row, {Smartsheet Whitelisted Domain Range 1}), IFERROR(MATCH([Requestor ]@row, {Smartsheet Whitelisted Domain Range 1})))

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 12/22/23
    Options

    Hi @dharberts

    It looks like you're wanting to use an INDEX(MATCH formula, to bring back the contents in an Approval column, is that correct?

    If so, you'll need the INDEX function in both of your formulas. Try a structure like this:

    =IFERROR(INDEX({Approval Column Reference}, MATCH([Requestor ]@row, {Requestor Column Reference}, 0)), IFERROR(INDEX({Approval Column Reference Sheet2}, MATCH([Requestor ]@row, {Requestor Column Reference Sheet2}, 0)), "Not Found"))

    Cheers,

    Genevieve

  • dharberts
    dharberts ✭✭✭
    Options

    Actually what I'd like to do is search the first sheet and if found there insert "Approved" if not there search the second sheet if found on that sheet insert "Not Approved" and if found on either insert "Not Found"

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @dharberts

    Thanks for clarifying! In this case, I would personally use a COUNTIF statement to count how many rows have your criteria. If the count is 0, it's not found, so it searches the other sheet. If that's also 0, it will return "Not Found".

    For example:

    =IF(COUNTIF({Smartsheet Whitelisted Domain Range 1}, [Requestor ]@row) <> 0, "Approved", IF(COUNTIF({Second sheet reference}, [Requestor ]@row) <> 0, "Not Approved", "Not Found"))


    I don't know what your reference name is for the second sheet, so you'll need to change out the {Second sheet reference} with your active reference.

    Cheers,

    Genevieve

  • dharberts
    dharberts ✭✭✭
    Options

    That worked, but I'd like to take it one step further and include if the cell is blank, this cell [Requestor ]@row, then return just a blank cell , no wording.

    Hope that makes sense?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Yes, no problem! Add that in as the first statement:

    =IF([Requestor ]@row = "", "", IF(COUNTIF({Smartsheet Whitelisted Domain Range 1}, [Requestor ]@row) <> 0, "Approved", IF(COUNTIF({Second sheet reference}, [Requestor ]@row) <> 0, "Not Approved", "Not Found")))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!