Formula similar to index/match, but between 2 values

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?

Find more posts tagged with

Accepted answers

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)

All comments

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.