Can someone help me with this IF INDEX MATCH formula?

I want to make a formula that searches another sheet and if the 1st budget is "Philadelphia", then show 1st budget amount. Otherwise, show the second budget amount.

Let me know if I am way off. Thanks!

=IF(INDEX({1st_Budget}, MATCH([Row ID]@row, {Row_ID})) = “Philadelphia”, INDEX({1st Budget Amount Approved}, MATCH([Row ID]@row, {Row_ID})), INDEX({2nd Budget Amount Approved}, MATCH([Row ID]@row, {Row_ID}))

Answers

  • MichaelTCA
    MichaelTCA Community Champion

    Hello @JRad

    Let's try this. It looks like you might be missing a closing ")" for the IF function and add the ",0" to the match functions to specify an exact match must be found.

    =IF(INDEX({1st_Budget}, MATCH([Row ID]@row, {Row_ID},0)) = “Philadelphia”, INDEX({1st Budget Amount Approved}, MATCH([Row ID]@row, {Row_ID},0)), INDEX({2nd Budget Amount Approved}, MATCH([Row ID]@row, {Row_ID},0)))

    If the Row ID occurs multiple times in your list, I recommend using the COLLECT function to add another criteria and simplify the results. Otherwise the first match found will be the result for all of the reoccurring Row ID's.

  • JRad
    JRad ✭✭

    Thanks for the response! That didn't seem to work, though. Still #UNPARSEABLE.

    What is the proper format of the COLLECT function? Maybe that is the fix.

    Thanks!

  • Paul Newcome
    Paul Newcome Community Champion

    The problem is your quotes around "Philadelphia". Notice how mine are straight up and down and yours are slanted? Yours are called "smart quotes" which (ironically enough) are not recognized as valid characters in Smartsheet formulas.


    Try retyping in your sheet directly, here in the Community, or in a text editor such as Notepad (not Word).

  • JRad
    JRad ✭✭

    Wow. Of course. 🤣

    All that time brainstorming and rethinking how to fix and it was just the other format of quotations.

    Thanks for your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!