how to use vlookup and if functions
Dear Smartsheet community members,
I need your quick support please. I am trying to pull the data from a sheet which is filled by operations team to the annual plan sheet. The task is to pull the actual contract values from Sheet of operations based on contract IDs to Annual Plan sheet. the complication is each contract has different items, using vlookup function allows to identify the same Contract IDs, however i am struggling to further isolate it to item level.
please let me know solutions.
here is an example
Answers
-
Hi Zafar,
You could use a JOIN(COLLECT formula for this, even though you don't have values to Join (you'll just be returning one cell, assuming that only one value meets all the criteria).
Try this:
=JOIN(COLLECT({Total Contract Value in Source Sheet}, {Contract ID Column in Source Sheet}, [Contract ID]@row, {Commodity Column in Source Sheet}, [Commodity]@row))
You can read more about each of these functions in our Help Center: JOIN function / COLLECT function / @row function
Let me know if this works for you!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Dear Genevieve,
thank you very much for your feedback. i did a quick test, in small scale seems works, now planning to test with real data. I will revert with the result.
one small issue i see with "join" formula is if someone moves the columns, the order of merging changes as well.
let me test in a bigger scale and revert.
wishing a great and productive week ahead.
zafar
-
Hello Zafar,
If you're talking about moving columns, as in shifting the position of the column in the sheet, then this is no problem as cross-sheet references will continue to reference the originally selected column, even if it moves to a different place.
Let me know if you have any questions as you go through your tests!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi Zafar, @Zafar Yuldashev
To add to @Genevieve P's excellent answer.
Be aware that when using cross-sheet formulas and VLOOKUP the columns that are included in the range can't be moved because it can break the formula or at least not show the expected result.
I hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Great point, Andrée!
Another reason why the JOIN(COLLECT is a better solution for this situation.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Dear Genevieve,
Thank you for this great solution, I was wondering though about the 100 cross sheet-reference limit, that I read about in an other post. Is that still the current limit? Could that limit cause issues if I use the formula you suggested for a similar purpose, but I have above 100 rows with 3 cells in each where I need to put the formula and all pulls the data from the same sheet? I would reach the limit and couldn't add anymore references, is that correct?
Thank you,
Dora
-
Hi @Dora Berky
The 100 cross-sheet reference limit refers to each unique reference, so for example:
=JOIN(COLLECT({Total Contract Value in Source Sheet}, {Contract ID Column in Source Sheet}, [Contract ID]@row, {Commodity Column in Source Sheet}, [Commodity]@row))
This has three unique references:
{Total Contract Value in Source Sheet} / {Contract ID Column in Source Sheet} / {Commodity Column in Source Sheet}
If you copy and paste this multiple times, it will still only have 3 unique references. What I believe you are referring to is the limit of 25,000 inbound cells that can be referenced. In this case, your example of 100 rows with 3 columns should be no problem because each reference will only be looking at 100 cells, for a total of 300 cells referenced.
If each of these unique formula ranges add together to look over more than 25,000 cells you will hit a limit. You can read more about this in our Help Center (see here).
Let me know if I can clarify anything further!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi Genevieve,
Thank you for the clarification, the formula seems to work well, it's good news that we won't reach the limit of cross-sheet references.
Have a nice day,
Dora
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!