Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel+training - Excel: Embedding a Index Function and If Statement Function
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
excel+training - Excel: Embedding a Index Function and If Statement Function
Resolved · Low Priority · Version Standard
Reply Mon 17 Dec 2007, 16:33Delegate Hina said...
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
For upcoming training course dates see: Pricing & availability
Reply Tue 18 Dec 2007, 11:08Delegate Paul said...
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)))
|