lesley burlingham
Member
I have the INDEX and MATCH formula on my sheet to return amounts from various tabs. Is it possible to add an IF staemetto this so it gives a message if the answer returned is 0.
Thank
Thank
Add IF statement to a INDEX and MATCH
- Thread starter lesley burlingham
- Start date
lesley burlingham
Member
Statement below.
=INDEX(INDIRECT("'"&C7&"'!B2:O26"),MATCH(Information!$C13,INDIRECT("'"&C7&"'!A2:A26")),MATCH(Information!C11,INDIRECT("'"&C7&"'!B1:O1"),0))
=INDEX(INDIRECT("'"&C7&"'!B2:O26"),MATCH(Information!$C13,INDIRECT("'"&C7&"'!A2:A26")),MATCH(Information!C11,INDIRECT("'"&C7&"'!B1:O1"),0))
Khalid NGO
Excel Ninja
Hi Lesley,
Instead of formula to return msg, we can use custom formats to return msg when the value is ZERO:
#,##0.00;-#,##0.00;"Your Msg"
Regards,
Instead of formula to return msg, we can use custom formats to return msg when the value is ZERO:
#,##0.00;-#,##0.00;"Your Msg"
Regards,
NARAYANK991
Excel Ninja
Hi Lesley ,
Your formula is quite a long one , and involves calculation of 2 MATCH functions and 1 INDEX function.
Assuming that this formula is designated as Your Formula , if you want to add an IF statement to check for whether the return value of this formula is 0 , then it will become :
=IF(Your Formula = 0 , Your Message , Your Formula)
which is a waste of calculations.
Khalid's suggestion to just use a custom format to display a custom message in a cell if it contains 0 will avoid this multiple calculation.
Narayan
Your formula is quite a long one , and involves calculation of 2 MATCH functions and 1 INDEX function.
Assuming that this formula is designated as Your Formula , if you want to add an IF statement to check for whether the return value of this formula is 0 , then it will become :
=IF(Your Formula = 0 , Your Message , Your Formula)
which is a waste of calculations.
Khalid's suggestion to just use a custom format to display a custom message in a cell if it contains 0 will avoid this multiple calculation.
Narayan