Hina has attended:
Project Intro Intermediate course
Excel Advanced course
Excel: Embedding a Index Function and If Statement Function
Hello,
I hope you can help me.
I have been creating a spreadsheet that helps me keep track of interactions between people along with dates. My spreadsheet has a column which i can mark 'complete' or 'Not Complete' depending on when that interaction needs following up or if it has been completed. What i need help with is look up the Complete or Not Complete values and then putting it on a status sheet.
I have managed to look up 'Not Completed' work but when all the cells in the column say complete I get an error "#N/A". This is the formulea i put in:
=INDEX(Follow_Up_Complete,MATCH("Not Complete",Follow_Up_Complete,0),1)
"Follow_Up_Complete" are named cells that contain the complete or not complete fields. I just named them to make it easier for me.
How do I handle the exception to the rule so that the result does not say "#N/A" Can I use an IF statement with an INDEX/MATCH function? How would i do that?
I look forward to hearing back from you.
Kind Regards,
Hina Patel
RE: Excel: Embedding a Index Function and If Statement Function
It's a bit of a messy formula, but the best way I find to get rid of #N/As is to do something like this:
(untested)
=if(isna(INDEX(Follow_Up_Complete,MATCH("Not Complete",Follow_Up_Complete,0),1),"",INDEX(Follow_Up_Complete,MATCH("Not Complete",Follow_Up_Complete,0),1)))