help with formula's

Alex Klingbeil

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.

Genevieve P.

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

Genevieve P.

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