Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

excel+training - excel embedding index function

Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel+training - Excel: Embedding a Index Function and If Statement Function

excel+training - Excel: Embedding a Index Function and If Statement Function

ResolvedVersion Standard

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)))

 

Training courses

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Turn Function tooltips on and off

Excel 2002 (XP) and Excel 2003 have the Function tooltips facility. When you type in a function name followed by a bracket, for example, =IF(, a yellow box appears beside the function name and lists the function's arguments. This is very useful when you can't quite remember the order of a function's arguments or what the arguments actually are!

However, Function tooltips can become annoying. To turn them off, choose Tools|Options. and select the General tab. Then, untick the Function tooltips box and choose OK.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.