how to use vlookup and if functions

Zafar Yuldashev

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

Genevieve P.

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

Zafar Yuldashev

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.

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å

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

Genevieve P.

Great point, Andrée!

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

Dora Berky

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.

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

Dora Berky

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