INDEX AND MATCH FORMULA, TO RETURN "0" WHEN THE VALUE IS "NO MATCH"

Mohammed Sulaiman

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.

Andrée Starå

Hi @Mohammed Sulaiman

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!