Referencing 2 sheets with different outcomes

dharberts

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

Genevieve P.

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

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.

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

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.

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