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
-
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!
-
@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.
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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 208 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 297 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!