Formula similar to index/match, but between 2 values

Options
Mtmoroni
Mtmoroni ✭✭✭✭✭

I could write a nested if statement, but my goal is to take a sheet that has quota ranges (see image). I want to take a cell that has a dollar value (say $12,175.25), and if it falls between 2 values, then take the column with the %. So with that example, I want 150% to be returned. If the number was $3,580, I would want to return 84%.


Otherwise my nested statement would be way long..... Any opinions?



Best Answer

  • Mtmoroni
    Mtmoroni ✭✭✭✭✭
    Answer ✓
    Options

    Fixed it! The "<" and the ">" were in the wrong positions! MANY THANKS!!!!!!! That saves so many nested ifs I have ever created!

    =INDEX(COLLECT({Quota - Lauren Payout}, {Quota - Lauren Minimum$}, <=[Commission MRR]@row, {Quota - Lauren Maximum$}, >=[Commission MRR]@row), 1)

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Mtmoroni

    An INDEX/COLLECT allows you to use multiple criteria instead of the singular criteria used in Index/Match

    I will assume your formula is within this same sheet. If it is a different sheet and you need assistance with a cross sheet reference, let me know.

    =INDEX(COLLECT(Payout:Payout, [Minimum$]:[Minimum$], >=[the dollar value ]@row, [Maximum$]:[Maximum$], <=[the dollar value]@row), 1)

    Kelly

  • Mtmoroni
    Mtmoroni ✭✭✭✭✭
    Options

    It is cross sheet. I referenced the columns on the other sheet, but get #invalid value.


    =INDEX(COLLECT({Quota - Lauren Payout}, {Quota - Lauren Minimum$}, >=[Commission MRR]@row, {Quota - Lauren Maximum$}, <=[Commission MRR]@row), 1)

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    oops, swap the less/greater than signs. My bad

  • Mtmoroni
    Mtmoroni ✭✭✭✭✭
    Answer ✓
    Options

    Fixed it! The "<" and the ">" were in the wrong positions! MANY THANKS!!!!!!! That saves so many nested ifs I have ever created!

    =INDEX(COLLECT({Quota - Lauren Payout}, {Quota - Lauren Minimum$}, <=[Commission MRR]@row, {Quota - Lauren Maximum$}, >=[Commission MRR]@row), 1)

  • Mtmoroni
    Mtmoroni ✭✭✭✭✭
    Options

    So - lets build upon this. While this works great - I want to add a new column that does the same thing - But looks 1 more row down. This way, i can create a column that shows what the next TIER is.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Mtmoroni

    I've done this using the Min row ID that is greater than the current row. I have the system auto-generated column in my sheet- and I add rows to the bottom and don't slide the rows around on the sheet.

    Would this work for you?