Nested Ifs that output VLookup results.

Hi folks, Back again.

I have a Pricing Tier dropdown that has three options. "Tier 1 - Small", "Tier 2 - Med", "Tier 3 - Large"

I now have a separate cell that needs to have a formula that checks the value in that pricing tier cell and based on a match, it pulls the output of a VLOOKUP. I can get it to work singularly, but anytime I attempt the nest all 3 together into one formula I break it. I'm not great with If/And/Ors.

The three singular formulas are below

=IF($[Pricing Tier]@row = "Tier 1 - Small", VLOOKUP($[Pricing Season]@row, {Price - VL Range}, 2, false), 0)

=IF($[Pricing Tier]@row = "Tier 2 - Med", VLOOKUP($[Pricing Season]@row, {Price - VL Range}, 3, false), 0)

=IF($[Pricing Tier]@row = "Tier 3 - Large", VLOOKUP($[Pricing Season]@row, {Price - VL Range}, 4, false), 0)

(Oh, and I need it to be Column formula compatible, or I'd be using index/collect which was a lot easier to deal with)

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I would suggest ditching the VLOOKUP for an INDEX/MATCH (more reliable, more efficient, more flexible.

    =INDEX({Range To Pull From}, MATCH([Column Name]@row, {Range To Match On}, 0))


    You can then nest the IF statement in the first portion of the INDEX function like so:

    =INDEX(IF($[Pricing Tier]@row = "Tier 1 - Small", {1st Range To Pull From}, IF($[Pricing Tier]@row = "Tier 2 - Med", {2nd Range To Pull From}, {3rd Range To Pull From})), MATCH([Column Name]@row, {Range To Match On}, 0))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • MPrice1
    MPrice1 ✭✭

    @Paul Newcome - I previously attempted that, but it wasn't compatible with column fomulas. Which is why I'm dealing with nested IFs

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Even trying the nested IFs with the VLOOKUPS in your original post will not work for a column formula because of the absolute reference "$" before the column names. Removing those should give you the ability to apply it as a column formula.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • MPrice1
    MPrice1 ✭✭

    Oh jeez. It was the $ the whole time?!?! GAH!

    The way the documentation was written I thought it was the "row index" component that was getting me.


    Switching back to my original Index/Collect formulas that were working. :D

  • AnnM
    AnnM ✭✭

    @MPrice1 Would you be able to provide your original formula using nested if/then/vlookups? I'm trying to do something similar and came across this post.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!