Can IF, Contains and VLOOKUP be combined?

Options
Mindfull
Mindfull ✭✭✭✭✭
in Formulas and Functions

Hi all,

I am trying to combine IF and CONTAINS with VLOOKUP (refer following) but it is throwing #INCORRECT ARGUMENT

=IF(CONTAINS("1", {Count}, VLOOKUP("TD", {Mastersheetrng}, 2)))

Can these be combined, or some other combination will work, please help.

Prajna 

· Share on FacebookShare on Twitter

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi Pranja,

    You can nest a COUNTIF statement within that IF statement to count and see if the number 1 appears somewhere in the {count} range. Then if it does, you can return the VLOOKUP:

    =IF(COUNTIF({Count}, CONTAINS("1", @cell)) >= 1, VLOOKUP("TD", {Mastersheetrng}, 2))


    If I've misunderstood what you're looking to do, and you have many 1's in the {count} range but you only want to use the LOOKUP on rows that contain a 1, then you may want to check out Paul's solution involving an INDEX(COLLECT in this other Community post, here.

    Let me know if this works for you!

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!