IF function - formula incorrect

[Michelle Brand Beast]

Hi guys,

So basically what I am trying to do is let Excel determine if what tier we are going to use automatically so it's like this:

If the given weight (column B) is less than, equal to, or greater than the range in column I, then it should correctly select the correct tier on column G.

I have tried this formula =IFS(B2<=I2:I6,"New - Tier One", "New - Tier Two", B2<=I2:I6,"New - Tier Three", "New - Tier Four", B2<=I2:I6,"New - Tier Five")
but it is not working. I am not sure if I am using the correct one.

Any help? I would appreciate it.

Thank you!

[#2]

Michelle-

I believe you can accomplish your task by using a Vlookup.  Please reference the below post and the example file that is included in it .  You will need to make a reference table based on your thresholds and then use the table in the vlookup to get the correct cost. You may have to make a few tweaks but it should be the same basic concept. :

https://techcommunity.microsoft.com/t5/Formulas-and-Functions/Formula-debug-am-i-using-the-wrong-for...

Ill also attach the file here for easier reference.

[#3]

Hi Matt,

Thank you so much for the quick response!

You're the best!

[#4]

Glad you were able to get your issue resolved.  Feel free to post back to the community if you have more questions or need further clarification.

[#5]

Hi Matt,

We can't make it work - I have attached a sample file. Could you please have a look at it?

Hoping that you could resolve it. Thank you.

[#6]

Hey Michelle-

Happy to help.  Try using the below formula.  I'm also attaching a sample workbook for reference.

[#7]

Hi Matt,

We have it working now!!!!

Thanks for all the help and keep rocking!

Michelle

[#8]

Michelle-

You're quite welcome.  Glad you were able to resolve your issue.  Please feel free to post back to the community for help on the next roadblock you encounter!  Always happy to help.