Dear Community,

I am trying to have an automatic filling of the column [Budget Max] while changing the column [Level]

based on 2 other columns references: [Level attribution] and [Max Budget Range] with matching ROWS.

If [Level]value = [Level attribution]value return [Max Budget Range]value of the same ROW of [Level attribution]matchingvalue.

This Formula works, but only for one row, while I would like it to work for the all sheet:

=IF(Level@row = [Level attribution]1, [Max Budget Range]1)

I have tried other possibilities below, but all of them returns either #Invalid Column Value or #Unparseable or # Incorrect Argument Set...

=COLLECT([Max Budget Range]1:[Max Budget Range]5, [Level attribution]1:[Level attribution]5, Level@row)

=IF([Level attribution]1:[Level attribution]5 = Level8, [Max Budget Range]@cell, "-")

=IF(Level@row = COLLECT([Level attribution]:[Level attribution], [Max Budget Range]:[Max Budget Range]))


Any suggestion would be of great help, thx!

NDA Strategic Plan boolean issue.PNG


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try something along the lines of this...


    =INDEX($[Max Budget Range]$1:$[Max Budget Range]$5, MATCH($Level@row, $[Level attribution]$1:$[Level attribution]$5, 0))

  • Michaël @ NDA

    Dear Paul,

    Thank you very much, that worked beautifully!

    I added an IFERROR to avoid a #NOMATCH return:

    =IFERROR(INDEX($[Max Budget Range (monthly)]$1:$[Max Budget Range (monthly)]$5, MATCH($Level@row, $[Level attribution]$1:$[Level attribution]$5, 0)), "")

    and extended this formula to another sheet for reference:

    =IFERROR(INDEX({NDA-VN Strategic Hiring Plan - 2019 Range 1}, MATCH($Level@row, {NDA-VN Strategic Hiring Plan - 2019 Range 2}, 0)), "")

    thx for the great help.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! yes


    Just a little suggestion... When referencing another sheet, I personally try to rename my ranges as "Sheet Name Column Name". So the Projected Complete column in my Rollup sheet as a cross sheet reference would look like {Rollup Projected Complete}. For me personally, it makes trouble shooting and tweaking a lot easier in the long run.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!