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

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.12 secs.