help with formula's

Options

hello new to smartsheets. i am looking to make a V lookup that will crosslookup on 2 sheet to tell me if it is on that sheet or not.

this is what i come up with so far.

=IFERROR(IF(ISBLANK($[Cable Name]@row), VLOOKUP($[Cable Name]@row, {K2 Power/Motor Cable List Tracker Range 1}, 1, false)) Motor, 0)

I would like it to say "Motor" if it is on the K2 Power/Motor Cable List sheet and "Instrument" if its on the K2 Instrument Tacker.

I cant seem to find a way to use 2 different Ref in 1 formula.

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Alex Klingbeil

    The way I would do this is actually to use a cross-sheet COUNTIF Function formula.

    This will COUNT if that specific value appears on Sheet 1, and if it does (or if the count is greater than 0), then return a specific text. However if it doesn't (so the COUNT is 0), we can then say, try this other COUNTIF formula looking at a second sheet!

    So for example, here's the first COUNTIF statement:

    =IF(COUNTIF({K2 Power/Motor Cable List Column}, [Cable Name]@row) > 0, "Motor"

    The {range} would only need to be the one column that has the Cable Names listed. (See: Cross-sheet formulas)

    Then we continue the formula, where if it's not greater than 0, we check the other sheet.

    IF(COUNTIF({K2 Instrument List Column}, [Cable Name]@row) > 0, "Instrument"

    Now, if neither of these formulas is True (they both say 0), we can say "Not Found" or something similar.


    Try this full formula:

    =IF(COUNTIF({K2 Power/Motor Cable List Column}, [Cable Name]@row) > 0, "Motor", IF(COUNTIF({K2 Instrument List Column}, [Cable Name]@row) > 0, "Instrument", "Not Found"))


    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Alex Klingbeil

    The way I would do this is actually to use a cross-sheet COUNTIF Function formula.

    This will COUNT if that specific value appears on Sheet 1, and if it does (or if the count is greater than 0), then return a specific text. However if it doesn't (so the COUNT is 0), we can then say, try this other COUNTIF formula looking at a second sheet!

    So for example, here's the first COUNTIF statement:

    =IF(COUNTIF({K2 Power/Motor Cable List Column}, [Cable Name]@row) > 0, "Motor"

    The {range} would only need to be the one column that has the Cable Names listed. (See: Cross-sheet formulas)

    Then we continue the formula, where if it's not greater than 0, we check the other sheet.

    IF(COUNTIF({K2 Instrument List Column}, [Cable Name]@row) > 0, "Instrument"

    Now, if neither of these formulas is True (they both say 0), we can say "Not Found" or something similar.


    Try this full formula:

    =IF(COUNTIF({K2 Power/Motor Cable List Column}, [Cable Name]@row) > 0, "Motor", IF(COUNTIF({K2 Instrument List Column}, [Cable Name]@row) > 0, "Instrument", "Not Found"))


    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now