Nested Ifs that output VLookup results.

MPrice1

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)

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

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

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.

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

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