Formula similar to index/match, but between 2 values

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?

Mtmoroni

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)

Kelly Moore

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

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

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

Mtmoroni

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

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

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?