# Formula similar to index/match, but between 2 values

Options
✭✭✭✭✭

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?

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

• ✭✭✭✭✭✭
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

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

• ✭✭✭✭✭✭
Options

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

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

• ✭✭✭✭✭
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.

• ✭✭✭✭✭✭
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?