Using IF OR MATCH to Check for Value in Multiple Sheets

cmilam

Hi,

I am trying to write a formula that will take a value from the sheet the formula is in and check for that value in other sheets. If the value is found in at least one other sheet, the formula should return "Y". If it is not found, it should return "N".

This is my current attempt, but this just returns "N" for everything.

=IF(OR(ISERROR(MATCH([Org Unit Code]@row, {Concourse_Assignments Range 1}, 0)), ISERROR(MATCH([Syllabus Assignments]@row, {Concourse_AssignmentEntries Range 1}, 0))), "N", "Y")

I am basing this formula off of the following. The below formula works, but it is only checking one sheet for a value instead of two (or more than two).

=IF(ISERROR(MATCH([Org Unit Code]@row, {Concourse_ContactInformation Range 1}, 0)), "N", "Y")

As soon as I try to add a secondary check to the formula, it breaks. I either get "N" for everything or "Unparseable" errors.

Any help would be greatly appreciated.

Note: I apologize if this is a duplicate post. I posted this question a short while ago, but when I came back to the site, it said I had no posts with my profile and I couldn't find it anywhere so I am not sure what happened.

SSFeatures

Hi @cmilam,

I tested out your formula on my sheet and made one adjustment. Instead of using OR I switched it to AND. The reason why I did this is because if the value is not found in {Concourse_Assignments Range 1} but it is found in {Concourse_AssignmentEntries Range 1} then we want "Y", instead of "N".

Try this out:

=IF(
    AND(
        ISERROR(MATCH([ID]@row, {Sheet A Range}, 0)),
        ISERROR(MATCH([ID]@row, {Sheet B Range}, 0))
    ),
    "N",
    "Y"
)

The only time that we want "N" is if we got an error from both sheet's ranges.

Hope this helps!

cmilam

Hi @SSFeatures

Doesn't using AND check to see if the value is in BOTH of the sheets' ranges? AND typically means that both values have to return TRUE in order for the equation to return TRUE. Doesn't that logic apply to Smartsheet formulas as well?

SSFeatures

Hi @cmilam,

Yes that's correct, that is how AND works. Just to be sure, this is the behavior that you mentioned that you wanted:

If the value is found in at least one other sheet, the formula should return "Y". If it is not found, it should return "N".

Remember that ISERROR will return TRUE if the value was not found in the sheet.

=IF(
    AND(
        ISERROR(MATCH([ID]@row, {Sheet A Range}, 0)),
        ISERROR(MATCH([ID]@row, {Sheet B Range}, 0))
    ),
    "N",
    "Y"
)

So with this formula, we will only return "N" if ISERROR returns true for both Sheet A and Sheet B. If ISERROR returns false for Sheet A then that means that the value was found in Sheet A, so the AND condition is false, and we return "Y". If ISERROR returns false for Sheet B then that means that the value was found in Sheet B, so the AND condition is false and we also return "Y" here.

Notice that the "N" comes before the "Y" in the formula. So "N" will only happen if the both conditions in the AND are true. This only happens if there was no match in both sheets.

Is this the logic that you wanted?

cmilam

@SSFeatures This does not satisfy the behavior I mentioned. "The value found in at least one other sheet" does not require it to be in both sheets. "At least one" other sheet implies that it only needs to be in one of the two sheets, not both.