Return a value based on two criteria.

Options
Carl Vieira
Carl Vieira ✭✭✭
edited 05/07/20 in Formulas and Functions

Hello Smartsheet Community!

First time posting a questions so here goes....

I need some assistance and am stuck with a formula. I have a Master File that looks at a lookup file for data.

I have Customer ID that have a Tier I rate and a Tier II rate based on a location. I already have in my file what Tier is expected but am having trouble bringing in the Max rate. I need to bring in the Tier Max Rate for that customer Tier so I can leverage a column that determines whether the requested rate is in under the Tier Max or not to see approval to proceed.

Each Picture is a separate sheet so the first picture would look at the second picture.


Any assistance would be greatly appreciated!

Carl Vieira

Best Answer

Answers

  • Carl Vieira
    Carl Vieira ✭✭✭
    edited 05/07/20
    Options

    Mark,

    That worked, I was discussing with my team and we knew it would have to probably have the Collect function but were stumped.

    Thank you very much for your insight.

    Best,

    Carl Vieira

  • Becky Wilson
    Becky Wilson ✭✭✭✭✭
    Options

    I am trying to get the following using Max(collect and can't seem to make it work:

    I want it to return the highest value for each Auction Item:


    In this instance, the formula would return $100 for Concealed Carry

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!