# 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)

• ✭✭✭✭✭✭

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))

• ✭✭

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

• ✭✭✭✭✭✭

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.

• ✭✭

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

• ✭✭

@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!