I can't get my Index Match Max formula to work- has error #UNPARSEABLE

Trying to find replica of excel's Xlookup formula.

Here is my current formula that is not working:

=INDEX({GP% TO PAY}, MATCH(MAX({HIGHEST TIER%})([% to GP Budget]@row, {HIGHEST TIER%}),0))

I am trying to pull in Employee's % to be paid on their bonus, based on the current sheet's % to GP Budget result. The helper sheet I am cross referencing is the template that I am telling it to look at the tier options and give me the max %.

Here is the sheet the formula is on:

and here are my cross references:

GP % to Pay=

Highest Tier%=

Best Answer

  • Matt Lynn-PCG
    Matt Lynn-PCG ✭✭✭✭✭✭
    Answer ✓

    @Krystal Garcia
    I added a 3rd reference for {Lowest Tier%} for Column 2 range. Then use this formula:
    =INDEX(COLLECT({GP% TO PAY}, {HIGHEST TIER%}, @cell >= [% to GP Budget]@row, {Lowest Tier%}, @cell < [% to GP Budget]@row), 1)

    Works on my end. Let me know if you need support and we can do a screen share meeting.

    Certified Platinum Partner

    2023 Partner of the Year

    PrimeConsulting.com

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!