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

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • 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

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    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.

  • Genevieve P.
    Genevieve P. Employee Admin

    Great point, Andrée!

    Another reason why the JOIN(COLLECT is a better solution for this situation.

  • 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

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!