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
-
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.
-
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!
-
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).
-
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!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!