INDEX AND MATCH FORMULA, TO RETURN "0" WHEN THE VALUE IS "NO MATCH"
Hi,
I'm trying to use the formulas both INDEX and MATCH, and trying to pull the budgeted revenue, by comparing the two project numbers in the sheets and returning the corresponding value.
See below formula:
=INDEX({Budgeted Revenue - November 2020}, MATCH([Project Number]@row, {Budgeted Revenue - Project Number}, 0))
Based on this formula,
If the project number is not present in the sheet, from where it is trying to pull the Budgeted Revenue - November 2020. It returns "NO MATCH".
How to ensure that the number 0 is returned instead of "NO MATCH".
TIA.
Answers
-
You'd use the IFERROR function to change what is shown.
Something like this.
=IFERROR(INDEX({Budgeted Revenue - November 2020}, MATCH([Project Number]@row, {Budgeted Revenue - Project Number}, 0)),0)
Did that work/help?
I hope that helps!
Have a fantastic day & Happy New Year!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Works, perfect.
Thanks for your help.
-
Excellent!
You're more than welcome!
✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!