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 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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Answers
-
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives