help with formula's
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

The way I would do this is actually to use a crosssheet 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: Crosssheet 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

The way I would do this is actually to use a crosssheet 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: Crosssheet 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